Query returning no field/result, need to show 0

[rbctemp]

Hi,



I need some assistance modifying the following query.

Query details:
Filters and counts 2 columns of a data based on 2 criteria:
Pipeline = “Level Number”
Name = “Y”
Both criteria must be met in order to be counted.

The query works fine if both criteria involve numbers, however if at least one field is blank/zero, the counter doesn’t return anything (no field at all // blank). I need the counter to return a “0” if the criteria aren’t met, yet still return the actual number in the case that it isn’t a zero.

I’ve tried using the Nz([field], 0) in the field section of my query but it doesn’t seem to work. Below is my code.

SELECT Count("") AS [Counter:]
FROM [Database]
GROUP BY Database.Pipeline, [Name]
HAVING (((Database.Pipeline)="Level Number") AND (([Name])="Y"));

Thanks.

[R_Badger]

I saw something before about this recently and using a custom function called nnz, I'll have a look and see if i can find the thread.

[R_Badger]

I found the thread about it here:

https://www.accessforums.net/reports...all-25075.html