DCount converse criteria

[DubCap01]

Hi all,

Happily using DCount to extract the answers where there is data/text in the field "Status":
=DCount("*","TblTmpEventDates","status = 'CANCELLED'")

But, how do I manage to count the Events in that table where the status is blank? I have tried:
=DCount("*","TblTmpEventDates","status = ''") I.e.two apostrophes, but to no avail.

Does anyone know how to count where there are blanks?

thanks in anticipation
Pete

[DubCap01]

Workaround, created a temporary table with all records that had IsNull("Status") situation, and then just did a DCount on the number of records in the table

Solved

[CJ_London]

or use the nz function


=DCount("*","TblTmpEventDates","nz(status,"") = ''")

[pbaldy]

or use the nz function


=DCount("*","TblTmpEventDates","nz(status,"") = ''")

Since they are inside a string, you can't use the double quotes in the Nz() function. I think it would need to be:

=DCount("*","TblTmpEventDates","nz(status,'') = ''")

[CJ_London]

well spotted!

[DubCap01]

well spotted indeed. Thanks both for your input, it works fine now without the need for the extra temporary table