Access: Counting the number of records with a blank field in Access 2003/XP/2000/97

Question:  In an Access 2003/XP/2000/97 report, how can I do a count of the number of items with a blank field?

Answer:  Let's take a look at a report to demonstrate.

In our report, we have an OrderDate field that we want to check for blank values (ie: no date value).

To do this, we first need to create a text box on the report and give it a unique name such as OrderDate2. Do not call the text box the name of the field that you are checking for a blank value.

Then you need to enter the following formula in the Control Source property:

=Sum(IIf(IsNull([OrderDate])=True,1,0))

This formula first calls the IsNull function to check if the OrderDate field contains a NULL value (ie: no date value). If the OrderDate field contains a NULL value, the IIf function will return a 1, otherwise a 0. The Sum function will add up all of the values returned by the IIf function. Thus, returning a count of the number of blank values in the OrderDate field.