Display 0 when counting yes/no field

[Arob]

I have the below query which counts the number of true values in the exclude location field in the orignal list table. However, how can you get the query to display zero value for a yes/no field?

SELECT Count([Original List].Excludelocation) AS CountOfExcludelocation
FROM [Original List]
WHERE ((([Original List].Excludelocation)=True))
GROUP BY [Original List].Blitzlocations
HAVING ((([Original List].Blitzlocations)=[Forms]![FrmMenuReports1]![blitzlocations]));

Thanks

 

[psemianonymous]

Try:

SELECT Nz(Count([Original List].Excludelocation),0) AS CountOfExcludelocation
FROM [Original List]
WHERE ((([Original List].Excludelocation)=True))
GROUP BY [Original List].Blitzlocations
HAVING ((([Original List].Blitzlocations)=[Forms]![FrmMenuReports1]![blitzlocations]));

 

[Arob]

The query still comes up with no results when their is only false results, I expect since the yes/no is not a null function, than the Nz function will not work.

 

[psemianonymous]

Oops. You're right--the query won't work. You should be able to get the result you're looking for like so:

Nz(DCount("Excludelocation","Original List","Blitzlocations = " & forms!frmMenuReports1!blitzlocations & " AND Excludelocation = True"), 0)

THIS should return 0 if all checkboxes are FALSE.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[Arob]

It wants to always give me a zero. Just wandered if you had a clue as to why it wants to do this?

 

[psemianonymous]

Maybe blitzlocations is a text field and not a numeric field? In that case, surround the value with quotes like so:

blitzlocations='insidequotes'

so the function becomes:

Nz(DCount("Excludelocation","Original List","Blitzlocations = '" & forms!frmMenuReports1!blitzlocations & "' AND Excludelocation = True"), 0)

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[Arob]

No luck, it still does the same. I will continue to mess around with it. If you happen to think of anything else let me.

Thanks for all the help. Arob