Counting Records based on criteria

[Pattyb628]

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]

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]

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]

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]

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]

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]

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