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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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)
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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

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

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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

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

this gives count of 5

mx

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

mx
davetoughAuthor Commented:
thank you
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.