Count query return zero instead of null/blank

[dhicks19]

Hi, first off I am totally new to access and vba.



I basically have a report fed from a count query. Heres my sql code for the query

SELECT TblDailyChecksArchive.Machine, TblDailyChecksArchive.CheckID, TblDailyChecksArchive.CheckName, TblDailyChecksArchive.MaxDay, TblDailyChecksArchive.Identifier, TblDailyChecksArchive.Complete, TblDailyChecksArchive.Reason, TblDailyChecksArchive.Department, TblDailyChecksArchive.Chkdate, TblDailyChecksArchive.Result, TblDailyChecksArchive.operator, TblDailyChecksArchive.LowerLimit, TblDailyChecksArchive.UpperLimit, TblDailyChecksArchive.NotRunning, TblDailyChecksArchive.Nights, TblDailyChecksArchive.carryover
FROM TblDailyChecksArchive
WHERE (((TblDailyChecksArchive.Chkdate)=IIf(Weekday(Date ())=2,Date()-3,Date()-1)));


I have uploaded pics of the returned result from query and the report I am wanting to use.

My problem is basically, we have a table that stores checks that need to be carried out daily. It stores whether they are complete or incomplete (-1) is completed and (0) is not complete.
I am trying to use a report that counts the checks, sees how many are complete and incomplete, then works out the percentage of completed check over the total amount of checks for that particular area ( cell 1,2,3,4).

E.g - If for instance cell1 has 164 checks in total, and 164 checks were complete, on the report it would show the 164 checks as completed but will not show incompleted checks as 0, because the query returns null and not a 0.

I need it to show 0 so I can work out the percentage properly.

Sorry, its hard to explain what the problem is. Any help would be appreciated.

[Robeen]

There is a Function called the 'Nz' function that you can use in your query [or in VBA] that you can 'tell' to return a 0 instead of a Null.
For instance - in Query Design, you could create a field named 'IncompleteChecks' - like this:

IncompleteChecks: Nz([IncompleteChecks],0)

I hope this helps. Let us know if you need more help.