Null values returned by count

[Mitch]

I am using a query to count then number of files with the status of complete.
When the count is greater than zero everything works properly. When there
are no values of "complete" I get nothing back. I think I need to use the nz
function, but can't make it work properly.

 

[Tom van Stiphout]

On Tue, 17 Feb 2009 23:20:09 -0800, Mitch

(you probably meant the number of records)
Typically queries will return 0 if the count is zero. For example this
query in the Northwind db will return 0 because a Status ID of 999
does not exist:
SELECT Count(Orders.[Order ID]) AS [CountOfOrder ID]
FROM Orders
WHERE (([Status ID]=999));

How is your situation different? Show us some code.

-Tom.
Microsoft Access MVP

 

[Mitch]

The database tracks apartment complex inspections. I used a created a query
to count the number of records in each inspection area. Each inspection also
has a status associated with it. When I try to count the number of records
where the status is, for example, "completed" and there are none, it does not
return a zero in the count field, it returns nothing. I don't know how to
show you the code as I am just doing this in the query design view and not
writing the code myself.

 

[John Spencer]

Open the query in design view
Select View: SQL from the menu.

Paste the SQL statement into your message.

Also do you have a table that contains the different statuses?

TblStatusValues
fieldStatus (values like reported, in progress, awaiting parts,
completed, etc)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================