Count records in Access report where a field has a specific value

Question

I have an Access report built on a query, and the [status] field for every record is either "Not late" or "Late". Is it possible without VBA to count the number that are "Not late" and the number that are "Late" and show it in the header of the report?

I set up a text box in the Detail section that equals 1 if [status] is "Not late" and another text box that equals 1 if [Status] is "Late," but I am having trouble running a sum against either of those text boxes. I thought I could use "=Sum[late_count]" in the footer (if the text box is named "late_count"), but that doesn't work--when I try to open the query, it asks for late_count.

I'm using "=Count(*)" to get the total number of records--is there a way to use Count but just against certain field values?

Solution

Have you tried in the footer:

=Sum(IIf(Status="Late",1,0))

Comments

[rryanp]

Thank you, but I was hoping to achieve it without modifying the underlying query, and Remou's solution worked to that end.

[Mus]

This worked for me after an hour of trying most other avenues - many thanks.