Counting Records by Status but NOT printing all records

[ChrisCione]

All records in my mdb are assigned 1 of 3 statuses: Pending, Active or Closed.


In the header of one of my main reports, I have 3 unbound textboxes to display the count of records for each status. For example, =Sum(IIf([RecruitmentStatus]="Closed",1,0)) and so on.


Here's what I am trying to accomplish:


While I want to show the count of records for EACH status (Pending, Active or Closed), I do NOT want to print any of the records in a Closed status.


In the query (the record source for the report), I unchecked "Show" for the Closed status, but it then will not count any of the closed records. I tried changing the report record source to the table, but I get the same results. If I filter out the Closed records, then it will not count.


I've spent a considerable amount of time Googling and have tried On Load and On Open Filters - all to no avail.


Is my ask possible?

 

[Joe4]

The "Don't Show" check box is just for fields (columns), not records (rows).

One option is to do two queries:
1. A Totals Query that returns the counts for each Status
2. A Select Query that returns all the records you want to print (filter out closed).

Then, create a Report for each, and put them on the same Report as Subreports.

 

[ChrisCione]

Thanks. I'll give this a whirl.

 

[Joe4]

You are welcome.
If you need any info on Subreports, just Google it, and there is lots of good information, tutorials and videos out there.
Using Subreports are a great way to get data from two separate data sources on the same Report.

 

[stumac]

Another way (Only works in the Print Preview view or when printing) is to hide the detail section on the Format event.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Detail.Visible = True  'I find it best to ensure that you set it to visible first
If Status = "Closed" Then Me.Detail.Visible = False
End Sub

The totals can then be calculated as a standard expression in the report footer:

=Sum(IIf([Status]="Active",1,0))
=Sum(IIf([Status]="Pending",1,0))
=Sum(IIf([Status]="Closed",1,0))

 

[ChrisCione]

Thanks. I'll also try this.