Return 0 when there are no results in query (A97 SR2)

Topic

New Reply

When running a query and there are no results, the query responds by returning no lines.
I want to have the query return a line with a zero when there are no results.

SELECT tblDiag.Status, Count(tblDiag.Status) AS CountOfStatus
FROM tblDiag
WHERE (((tblDiag.DateOfCloseCall) Between [Start Date] And [End Date]))
GROUP BY tblDiag.Status
HAVING (((tblDiag.Status)=”Open”));

Reply | Quote

Replies

That isn’t the nature of queries. Queries return records based on the SQL you passed them. They can’t return non-existent records.

Reply | Quote

As Charlotte pointed out, a query can’t do that. In your example, what should Status be in a “record” with CountOfStatus 0?

I suppose that you want to use this in a form or report.

There, you might work around it using DCount. This will return 0 if no records meet the condition argument.

And in (sub)reports, you can check for HasData = 0.

Reply | Quote

Thank you, the both of you.

I will strive to do this on the report instead of the query.

Reply | Quote