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.

Regards,
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.