Counting Null Fields

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hello,

I am using the group by option in the query and then a
count on the different group by's which are created? One
of the values is null? How do I get a count for the null
value?
Ex:
Type Count of type
null 0 should be 125
a 50
b 75
Thanks
 
Hi,


SELECT COUNT(*) - COUNT(FieldName)
FROM myTable



returns the number of null under FieldName, since COUNT(*) count all the
records, null included, and COUNT(field) just count the not-null values.



Hoping it may help,
Vanderghast, Access MVP
 
I don't believe you can count something that is not there
(i.e. null). Instead of counting your type value, try
counting your primary key value which will always be
populated:

SELECT Count(Table1.ID) AS CountOfID, Table1.type FROM
Table1
GROUP BY Table1.type;

If you want to have the word "null" printed under "type"
as you have listed below, use:

SELECT Count(Table1.ID) AS CountOfID, Table1.type, Switch
(IsNull([type]),"null",Not IsNull([type]),[type]) AS Expr1
FROM Table1
GROUP BY Table1.type, Switch(IsNull([type]),"null",Not
IsNull([type]),[type]);

Someone else may have a cleaner way of doing this, but at
least it works. Good luck.
 
Back
Top