We help IT Professionals succeed at work.

how to count null values in query

davetough
davetough asked
on
I have attached db:
open db
open query Gqry3
you will see in number field 4 ( there are actually 5 records) is there a way to count the null values too in a query like this?
thank you
db1.accdb
Comment
Watch Question

Do you have a field that has a value in each record (like a primary key field)?  You can count on that with an Is Null on the field with nulls.

Alternatively, you can use SQL like this: SELECT Count(*) FROM tblName WHERE (FieldName Is Null)
Database Architect / Application Developer
Top Expert 2007
Commented:
Try this:

SELECT GoldFedExTable.[Pickup Date] AS Expr1, Count(Nz([Ref 1],1)) AS [Number], Sum(GoldFedExTable.Charge) AS 7445
FROM GoldFedExTable
WHERE (((GoldFedExTable.[Ref 1])="7445")) OR (((GoldFedExTable.[Ref 1]) Is Null))
GROUP BY GoldFedExTable.[Pickup Date];


mx
Most Valuable Expert 2012
Top Expert 2013

Commented:
Give this a try:

SELECT GoldFedExTable.[Pickup Date] AS Expr1, Count(Nz([Ref 1],0)) AS [Number], Sum(GoldFedExTable.Charge) AS 7445
FROM GoldFedExTable
WHERE GoldFedExTable.[Ref 1] ="7445" OR GoldFedExTable.[Ref 1] IS NULL
GROUP BY GoldFedExTable.[Pickup Date];

Open in new window

Most Valuable Expert 2012
Top Expert 2013

Commented:
Wow.  I didn't see that last post when I refreshed the screen.  Sorry about that.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
wow!
Don't think you want Count(Nz([Ref 1],0))
                       .......................................^

Count(Nz([Ref 1],1)
                            ^

this gives count of 5

mx

Most Valuable Expert 2012
Top Expert 2013

Commented:
mx

Not sure what you're talking about -  1 or 0?   Or the parentheses?  

Both statements work exactly the same way and give you the same correct results (you got it first, of course)

Count(Nz([Ref 1],100000)  would give you the same results too.

The "value if null" simply gives you something to count, since Nulls don't add to the Count.  You can use anything you want other than null and still get the same results.  

(If it were SUM and not COUNT, it would make a difference - but with COUNT there is no difference)
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
yep ... I thought I had tried Nz(x,0) at first and only getting 4 ... but forgot the Or Is Null

mx

Author

Commented:
thank you