How to count individual items in a value list

[Bob Lee]

Access 2010. I have a drop down list with "Male" or "Female" as the choice. I wish to count the number of Males and the number of Females to show on a report. The Field name is [Gender] which is stored in a table.

Is there a code to use in an unbounded text box that will show the totals on a report? I have tried =Count(If[Gender])="Female")) and it returns 'Error' as the value.

Thank you,
Bob

[colintis]

As you said, the gender data is store in a table, so you'll need a query to run instead. Something like this:

select count(*) as total, 
  sum(iif(gender = 'male',1,0)) as male, 
  sum(iif(gender = 'female',1,0)) as female 
from mytable

[NeoPa]

In your footer you can use the standard aggregate functions (Count(), Sum(), etc), but they can only refer to controls on your report object, not to the fields of the record source.

The items that are aggregated will depend on the level of the footer involved.

Welcome to Bytes!

[NeoPa]

Actually, I may have missed the more obvious problem with your attempt. If the control is called [Gender] (rather than just the field) then your code would need to be something like :

=Count(IIf([Gender]='Female',[Gender],Null)

or even :

=Sum(IIf([Gender]='Female',1,0)

[Bob Lee]

Yes,that works. Thank you to Neopa and Colintis for your help. I appreciate it.

[NeoPa]

It's a pleasure Bob. I'm glad it helped :)