Counting Records by Status but NOT printing all records

ChrisCione

Board Regular
Joined
Aug 27, 2008
Messages
83
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?
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jul 16, 2010
Messages
471
Another way (Only works in the Print Preview view or when printing) is to hide the detail section on the Format event.

Code:
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:

Code:
=Sum(IIf([Status]="Active",1,0))
=Sum(IIf([Status]="Pending",1,0))
=Sum(IIf([Status]="Closed",1,0))
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,113,875
Messages
5,544,808
Members
410,635
Latest member
phoenix7771
Top