Need Query to display a Zero when no rows are present

[Nphp0729]

I am a beginner to Access and learning more and more as I try. I have a query which gives me a count of Products but I need it to display a zero when there is no data giving me no rows. The query I have set up goes something like this.

Name--------------Product--------Brand--------Due In-------On Hand
Group by ----------Count -------Where --------Where-------Where

The count I receive from this query is then added to another query but when there is no data here the other query will not run either.

 

[ajetrumpet]

I am a beginner to Access and learning more and more as I try. I have a query which gives me a count of Products but I need it to display a zero when there is nothing. The query I have set up goes something like this.

Name Product Brand Due In On Hand
Group by Count Where Where Where

The count I receive from this query is then added to another query but when this displays a zero the other query will not run either.

you need to use an IIF() statement, like this:

iif(dcount() = 0, 0, count())

the dcount() obviously changes, but I'm sure you can get the idea from this. you only need one field. just make sure the WHERE clause in the dcount() is correct.

 

[Nphp0729]

I've played with your suggestion but still don't get the desired result. I'm sure I'm doing something wrong. Here is the SQL of the Query as it is now. I need it to display a zero in the COUNT([Product List].Product) Field because when the result of the count =0 I don't receive any information.

SELECT [Product List].NAME, Count([Product List].Product) AS [Products Line Items]
FROM [Product List]
WHERE ((([Product List].Brand Name)<>"Whatever") AND (([Product List].[Due In])=0) AND (([Product List].On Hand)>0))
GROUP BY [Product List].Name;

 

[ajetrumpet]

what about this:

SELECT [Product List].NAME, IIF(Count([Product List].Product) = 0, 0, Count([Product List].Product)) AS [Products Line Items]
FROM [Product List]
WHERE ((([Product List].Brand Name)<>"Whatever") AND (([Product List].[Due In])=0) AND (([Product List].On Hand)>0))
GROUP BY [Product List].Name;

 

[Nphp0729]

I tried the above sql code but I still receive no results. I don't know if I'm being clear. My query is set up to always return one row of information. I need the count of the products with the above set criteria. If there is no items that meet this criteria I don't receive any information. I need the row to always display with a count even if there is no information with the set criteria.

 

[wilpeter]

Adam: He's not getting 0 as the Count because there are no records being counted, so the function doesn't happen. There would have to be a previous verification that there were records to count...like a select query without totalling.