Need count Query to output 0 (zero) when there are no matching records

[chris01252]

I have a form where a user reviews information input by another user, once they have done this they sign it off by selecting their name from a drop down list. On this form there is a scrolling message which tells the user how many un-signed entries there are. This works fine until there are 0 (zero) entries to be signed off.

The scrolling message is linked to a count query which basically counts any records that does not have a name entered in the required field. I have done this with ' Not Like "*" '

Like I say it works when there is one or more entries to count, but as soon as there are none the query does not output a zero it is just blank. This is the problem.

I need some sort of statement to say if there are no matching records please display a zero.

Any help would be much appreciated!

 

[neileg]

You need to construct a query that returns all the possible values that might have this count associated with them. Make sure this is set to Unique Values.
Add this to your existing query and use a Left join to join this to your existing table(s). A left join is the one that returns all of the records from the new query and any values from the other data that match.

This will esnure that you get the null counts returned. You can use Nz() to turn the nulls to zeros.