Report not running. 1

[needadvice]

I have a report that runs off a "Master Query" which is composed of four small queries. Each pulls a count of records with a particular "Code". I enter two dates on a form and it pulls the count of records between the dates.
The problem is one of the codes has no records for the choosen dates. As a result the Master query does not run and the report comes up blank. How do I get the query to pull up Zero for no records. Here is the SQL for the offending query.

SELECT MailLog.Code, Count(MailLog.Code) AS CountOfCode
FROM MailLog
WHERE (((MailLog.Date) Between [Forms]![MonthlyReportsDates]![StartDate] And [Forms]![MonthlyReportsDates]![StopDate]))
GROUP BY MailLog.Code, MailLog.Code
HAVING (((MailLog.Code) Like "L&quot);

My usual sources at work have not figured this out, any help greatly appreciated.

 

[PaulBricker]

Probably the best thing is to go back to the 4 queries and wrap your values in the Nz([FieldName],0) function. Then do your summeries from there. You will have to use aliases' for the field names or Access will put in it's own Expr1, Expr2...ExprN. You can't reuse the actual field name if you wrap it in an Access Function.

Paul

 

[needadvice]

That worked. In addition, I had to restructure my query. I made it too complex. I didn't need the group by clause nor the code field. After that the NZ function worked.
Thanks

 

[PaulBricker]

Glad you worked it out.
Good luck.

Paul