USING COUNT in Reports

[krisboy]

Afternoon all!

I'm trying to use the COUNT function to display the number of records in a group footer on a report where values in one of the fields = 1. In short, does anyone know how to specify criteria in a group footer using COUNT?

I've looked briefly at using DCOUNT, but this gave a result from the source table, rather than from the group that I need to show the result for....

Cheers.

Chris.

 

[allanon]

I tried realy quick on a table that I played with and this is what you do in the control source of the footer field:

=Count([detail table_field])

 

[krisboy]

I don't suppose you (or anyone) could elaborate on that a bit - i'm only a mere Access rookie, and I still can't get it working... Every time I try any kind of COUNT expression, I get the total number of records for the group, rather than the number of specified records.

Many Thanks.

Chris.

 

[allanon]

Ok. I think I know what you want. You want to only count the number of times the field has a "1" in it. Is this correct? If so, try this in the control source property in your footer field:

=sum(iif([field_name]=1,1,0))

 

[krisboy]

That works beautifully....!!

You're a genius and a gentleman...!

Cheers.

Chris.