Report total using an IIF statement

[buchent]

I put the following in a text box on my report, but keep receiving an error message of "The expression you entered has a function containing the wrong number of arguments". What have I done wrong? How can I fix this?

=Count(IIF([Tpye of Scholarship]="Line"))

Thanks,
TIM

 

[Pat Hartman]

When you use an IIf() function you need to supply all three parts - the condition, the true expression, and the false expression. Also, Count() is the wrong function to use for this purpose.

=Sum(IIF([Tpye of Scholarship]="Line",1,0))

The above expression will add 1 if the condition is true and 0 if it is false. This will have the effect of counting the number of times "Line" occurs.

 

[buchent]

Pat,
Thank you very much. It works great!

 

[dkirk02]

Pat,
I have been working on a similar problem...
In a report, I have [category] with four possibile choices in a detail header. I want to be able to count how many times each type occurs on the report in a calculated field on the report footer.
At this point, I can't even get the count function to work for the [category] field by itself, let alone counting each type. I tried Sum as you suggested above, but that gave me an invalid data type error since the field is a text field, not a number. Any help here would be greatly appreciated.

Thanks in advance,
Deeta

 

[Pat Hartman]

Deeta,
Add four unbound textboxes to the report footer section. In each one check for a different category.

=Sum(IIF([Category]="A",1,0))

=Sum(IIF([Category]="B",1,0))

The Sum() expression above is not trying to sum a text value, it is summing a numeric value - 1 or 0. Your problem is most likely caused because you re-used a previously bound control and the control name was left as the name of the column from the recordsource that it was originally bound to.

 

[dkirk02]

Pat,
Man, I think you're on the right track here. That almost works....one more question though.
On my report I have case id# and then each case id can have multiple accounts tied to it. The set up is this: [Case id] and [category] are in the case id header and the account information is in the detail section. When I use the formula you suggest, it counts not only the real category entry, but also counts one for each additional account that is tied to the case id. So, if #2001 is "A" category and has three accounts tied to it, the formula will count that as 3, even though category should only be counted once per case id. Any thoughts?

Thanks in advance for all the help!

 

[Pat Hartman]

You'll have to do the counting yourself with VBA. Define 4 integer variables. Add a case statement to the format event of the case group to do the counting. Only execute the code when the formatCount property is equal to 1. Then print the accumulators in the report footer.