Count IIf Is NULL

[davea300]

I'm trying to use an unbound text field to count all records in a report which have no value (i.e. is null)

So far I've got =Count(IIf([JCONTNO]=Null,0)) but it returns 0. I've tried variations but still no joy. Could someone point me in the right direction?

(Using access 2003)

 

[Beetle]

You can't use a comparison operator (=, >, <, etc.) on a field that is Null because Null cannot be compared to anything (even another Null). A field either is Null, or it isn't. Also, the IIf function requires two arguments, truepart and falsepart. Your expression should probably look more like;

=Sum(IIf([JCONTNO] Is Null, 1, 0))

Or you could use DCount;

=DCount("*", "YourTable", "[JCONTNO] Is Null")

 

[davea300]

Excellent thanks, just what I was looking for. Used the SUM function.