Counting Null and Not Null values in one query

[Amy Johnson]

Is there a way in one query to count the null and not
null values for the same field? For example I have a
field named Comments. I want the query to count how many
records have a comment and how many records do not. Any
suggestions?? If I can not do it in one query is there
another way? Something I will be able to understand?

Thank You

 

[MGFoster]

Is there a way in one query to count the null and not
null values for the same field? For example I have a
field named Comments. I want the query to count how many
records have a comment and how many records do not. Any
suggestions?? If I can not do it in one query is there
another way? Something I will be able to understand?

SELECT Count(IIf(Comments IS Null, 1)) As NullCount
Count(IIf(Comments IS NOT NULL, 1)) As NotNullCount
FROM table_name

 

[Guest]

I keep geting syntax error when I try to creat the
suggested query.

It says "Syntax error (missing operator in query
expression 'Count(IIf(Follow Up Date IS Null, 1))'

Any suggestions??

Thanks Again!!

 

[Ken Snell [MVP]]

Try this:

SELECT Sum(IIf(Comments IS Null, 1, 0)) As NullCount
Sum(IIf(Comments IS NOT NULL, 1, 0)) As NotNullCount
FROM table_name;

 

[Amy]

I still keep getting the same error message. I must be
doing something wrong.

 

[Duane Hookom]

Any time you make the mistake of creating field names with spaces, you have
to enclose the field name in []s.

 

[Steve Schapel]

Amy,

As Duane indicated, you need...
IIf([Follow Up Date] Is Null, 1, 0)
or...
IIf(IsNull([Follow Up Date]),1,0)

Depending on what you want to do with the results of this query, this
may be more useful....

SELECT IIf(IsNull([Follow Up Date]),"Nulls","Not nulls") AS NullTest,
Count(*) AS HowMany
FROM YourTable
GROUP BY IIf(IsNull([Follow Up Date]),"Nulls","Not nulls")