Search code examples
ms-accessreport

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

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))