how to count null values in query

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
davetoughAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Luke ChungPresidentCommented:
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)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mbizupCommented:
Wow.  I didn't see that last post when I refreshed the screen.  Sorry about that.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
wow!
Don't think you want Count(Nz([Ref 1],0))
                       .......................................^

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

this gives count of 5

mx

0
mbizupCommented:
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)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
yep ... I thought I had tried Nz(x,0) at first and only getting 4 ... but forgot the Or Is Null

mx
0
davetoughAuthor Commented:
thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.