Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    Newark, New Jersey, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Null Value in a query (Access XP)

    My first query determines whether a job was "Hit" or "Missed" by comparing the actual finish date to the job's due date. The second query counts the total number of "hits" and "misses" in each of our 4 divisions. The final query combines the number of "hits" with the number of "misses" along with the total number of jobs, and determines the percentage which were hit.

    This all works fine as long as each division has at least 1 hit and 1 miss. However, if for instance I have a division with 3 hits, and no misses, it will not display nor calculate in the 3rd query since the "missed" column is null.

    I've tried creating various joins in all three queries, but haven't had any success. Is there a way to have the 2nd query return a 0 (zero) if it does not count any "misses"? Or if anyone has any other thoughts to remedy this problem, I'd appreciate your input.

    Thanks!
    Rich

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Null Value in a query (Access XP)

    Create a table with one Yes/No field (primary key). Create two records in this table, one with a Yes value, the other with a No value. Or if your "Hit" and "Miss" results are strings, make that a text field with values "Hit" and "Miss". Create a query based on the first one you had plus the new table, linked on the Hit/Miss field. Double click the link and select the option to return ALL records from the table. This will always return a Hit and a Miss record.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Null Value in a query (Access XP)

    It would seem that using the NZ( ) function in that 2nd query where you total the hits and misses would get a 0 into any column instead of a null.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •