Counting Records based on criteria (1 Viewer)

Pattyb628

New member
Local time
Today, 15:59
Joined
Jan 5, 2005
Messages
5
Hello,

I am trying to count records in a report based on other fields in the report.

I have two fields: [Service], and [Service Month]

Basically, I want to place a text box on the report that counts the number of records where [Service]="ASSESSMENT" & [Service Month]="7"

I put this in the control source of the text box on the report:

=Count((IIf([Service]="ASSESSMENT" & [Service Month]="7",0)))

However, this doesn't seem to be working. I would rather not do the count in the query that feeds the report becuase I would like to use the report to count other fields as well.

Is there another way to do this?
 

Pattyb628

New member
Local time
Today, 15:59
Joined
Jan 5, 2005
Messages
5
Dcount

Okay, I think I have to use the DCount function.

So far, I have this:

DCount("*", "[Vouchered & Non-Vouchered Services]","[Service]= 'ASSESSMENT' And [Service Month] ='7'")

But this gives me a syntax error (missing operator).

Anyone know what I'm missing!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:59
Joined
Feb 19, 2002
Messages
28,612
You are using the ampersand when you need to use the "AND" operator.

=Sum(IIf([Service]="ASSESSMENT" AND [Service Month]="7",1,0))


The Count() function always counts the entire domain so if you have 100 records in the recordset, the answer will be 100 regardless of how many actually match the criteria. The Sum() works better for this purpose. The statement adds 1 if the condition is true and 0 if it is false so it "counts" the true cases.
 

Pattyb628

New member
Local time
Today, 15:59
Joined
Jan 5, 2005
Messages
5
That worked perfectly.

I'm pretty sure it would have taken me a long time to figure that out.

Thanks Pat! You're the best!
 

Gordon

Gordon
Local time
Today, 23:59
Joined
Nov 25, 1999
Messages
34
Counting Records in a Report, based on a Criteria

You are using the ampersand when you need to use the "AND" operator.

=Sum(IIf([Service]="ASSESSMENT" AND [Service Month]="7",1,0))


The Count() function always counts the entire domain so if you have 100 records in the recordset, the answer will be 100 regardless of how many actually match the criteria. The Sum() works better for this purpose. The statement adds 1 if the condition is true and 0 if it is false so it "counts" the true cases.
I just wanted to add a big "thank you" as well. I was trying to get a report to count records, based on a simple criteria and was having a heck of a time. Here is what I ended up with, in case you are trying to do the same thing!

=Sum(IIf([Report]="c",1,0))

"Report" is one of the fields in the report, and "c" is the criteria I am trying to count. Simple enough ..... once you know how!
 

NauticalGent

Executive Troll
Local time
, 00:59
Joined
Apr 27, 2015
Messages
2,612
You are using the ampersand when you need to use the "AND" operator.

=Sum(IIf([Service]="ASSESSMENT" AND [Service Month]="7",1,0))


The Count() function always counts the entire domain so if you have 100 records in the recordset, the answer will be 100 regardless of how many actually match the criteria. The Sum() works better for this purpose. The statement adds 1 if the condition is true and 0 if it is false so it "counts" the true cases.
Excellent reply Pat (as usual). Learn something new every day...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:59
Joined
Feb 19, 2002
Messages
28,612
You're welcome. This is a pretty old thread but it looks like it surfaced again. My husband used to sometimes help his co-workers with Access and always got a kick out of pulling up one of my old posts with the answer :)
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom