Count Nulls & Not Nulls in Reports

[RoadKill]

I am trying to figure out how to count the Null values and Not Null values in
a field in a report.

We'll just call it Field3 of Query1 of Report1.

Thank you

 

[John Spencer]

In a control in the report footer set the control source to

To Count not nulls all you need is the following. Count counts any
value that is not null and does not count Null values.
=Count(Field3)

To Count Nulls you can test if the field is null and return a value if
so, otherwise return null if there is already a value
=Count(IIF(Field3 is Null,1,Null))

Or count nulls using the following.
=Count(*) - Count(Field3)

Count(*) counts all the records and then you subtract the records that
have a value in field3

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[RoadKill]

Great, thanks John!

In a control in the report footer set the control source to

To Count not nulls all you need is the following. Count counts any
value that is not null and does not count Null values.
=Count(Field3)

To Count Nulls you can test if the field is null and return a value if
so, otherwise return null if there is already a value
=Count(IIF(Field3 is Null,1,Null))

Or count nulls using the following.
=Count(*) - Count(Field3)

Count(*) counts all the records and then you subtract the records that
have a value in field3

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================