Counting Nulls

[Kennedys21]

I have a query that I am trying to count how many times a record appears in a
field. The issue is there are some where the record is null, and it won't
count these. Is there a way count nulls, or give the nulls a value?

 

[Jeff Boyce]

I'm a bit confused by the terminology...

A "record" in Access is the entire row in the table, with all the fields. A
"record" would not "appear in a field".

Thus, a "record" could not be null, as this would imply that there was no
record.

If you have a field that may contain a Null, you could use a query and
create a new output field in that query that displays something like
(untested):
YourNewOutputField: Nz([YourFieldThatMayHaveNulls),0)
to get a zero instead of a Null.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

Perhaps you just want to use Count(*) to count the number of records
returned.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Michel Walsh]

SELECT COUNT(*) AS numberOfRecord,
COUNT(fieldName) AS numberOfRecordWithAValueUnderFieldName,
COUNT(*) - COUNT(fieldName) AS numberOrRecordWithANullUnderFieldName

FROM myTable

Since you can use the aggregate, here COUNTs, in an arithmetic expression,
here, a subtraction.

And, sure, you can 'change' a null to something else with Nz( a, b ),
which is a function returning a, unless a is null, then it returns b. So,
Nz(fieldName, 0 ) returns the value under the field, except if that value
is null, then, it returns zero.



Vanderghast, Access MVP