Full Version: Count in Query
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
I want the query to display a 0 if the count is 0. Currently, it displays nothing. How do I fix this?
Here is an example of my SQL:
SELECT Count([ExtendedPrice]) AS [CountOfExtendedPrice] FROM [Order Details Extended] GroupBy [ExtendedPrice] HAVING ((([ExtendedPrice])="100.00"));
So it counts how many times it finds extended price being 100.00
If there are no 100.00's, I want it to say 0 but it currently says nothing at all.
Any help would be greatly appreciated, thanks.
as in, where does the "NZ" function go?
SELECT NZ(Count([ExtendedPrice]),0) AS [CountOfExtendedPrice] FROM [Order Details Extended] GroupBy [ExtendedPrice] HAVING ((([ExtendedPrice])="100.00"));
That didn't work for me, not sure why. Let me give you my exact SQL (that was merely an example)
I am doing a survey, and they can select one of four answers for this question. I want to count how many times "not available" is selected, but if it is 0, i want it to put 0 instead of nothing.
Now keep in mind that I work for law enforcement....
SELECT Count(Contacts.[Q2a - Powder Cocaine]) AS [CountOfQ2a - Powder Cocaine]
FROM Contacts
GROUP BY Contacts.[Q2a - Powder Cocaine]
HAVING (((Contacts.[Q2a - Powder Cocaine])="Not Available"));
Thanks again!
The problem is that your query is not returning a null, It is actually returning an empty set (i.e. zero rows/columns returned). If you had a null return, the query would have worked.

you will have to provide more detail about the layout of your table in order to assist you with a working query.

Richard Broersma Jr.
Edited by: rabroersma on Fri Feb 9 19:48:18 EST 2007.
Ok, what do you need to know about the layout?
By layout I mean table definition. Knowing what columns are in your table will help in determining how to construct a outer join query. Using the outer join query, a null will be returned that can be converted to a zero for you total count.
There are basically just four answers they can choose from in a drop down menu. I just am counting each selection from all the surveys
Can I put an "If" statement in SQL, so that if the value is null, it will put a 0?
What are the tables that you are using to populate the drop down menu box?
Edited by: rabroersma on Fri Feb 9 20:22:17 EST 2007.
An IF has the same problem as the NZ. It can't act on a emptyset. It needs a null (at the very minimum) to operate on.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.