Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2001
    Newark, New Jersey, USA
    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.


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 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
    Crystal Beach, FL, Florida, USA
    Thanked 40 Times in 39 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