Problems with IIF in Access Report

[gjmac2]

I have a count field which supplies a value of 1 for each record in my query. I also have a region field which identifies the record by their geographical location.

I have grouped together all the records, and and am trying to perform the following:

1. Total sum of the count field.
2. Sum of the the count field based on the region field value.

The expression I ham having problems with is:

=IIf(([Region])="West",Sum([Count]),null)

All I am getting is null. However, the expression:

=Sum([Count])

works just fine. Any suggestions as to whazt I am doing wrong?

 

[traingamer]

Just put

=Sum([Count])

in the group footer to get the results for that group and again in the report footer to get a total for all. Why bother with an iif?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[dhookom]

It isn't clear how your report is grouped and what you want to calculate. It would help to see some sample data and desired display in your report.

If you want to count the number of records in a group or report where the Region field = "West" then use a text box with a control source of:

=Sum(Abs([Region]="West"))

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]