Sum IIf Multiple Criteria

[RogerC]

I want to create a calculated field in an Access report using the Sum IIf function with multiple conditions.



When only one condition is used, the formula works just fine: =Sum(IIf([Balance]="ABC",[Amount],0))

Similarly, I get the correct results when I plug in the other condition, =Sum(IIf([Balance]="XYZ",[Amount],0))

But when I combine the two conditions with an Or function, nothing happens.
=Sum(IIf([Balance]="ABC" Or [Balance]="XYZ",[Amount],0))

Am I using the wrong syntax?
Thanks in advance.
Roger

[pbaldy]

The syntax looks fine to me. By nothing happens, you mean the textbox is blank? Can you post the db here?

[ssanfu]

Looks right to me also. But if the control is in the detail section, I think (could be wrong ) you would use

=IIf([Balance]="ABC" Or [Balance]="XYZ",[Amount],0)

In the footer, use
=SUM(Text32)

where Text32 is the name of the control that has the IIF() expression.


Or you could try

=IIf([Balance]="ABC" Or [Balance]="XYZ",[Amount],0)

and set the "Sum Over All" or "Sum Over Group" property for the text box. (Running sum??)

------------
Things work differently in a report.

[pbaldy]

In the footer, use
=SUM(Text32)

where Text32 is the name of the control that has the IIF() expression.

Unless something has changed, you can't sum a calculated control:

http://support.microsoft.com/kb/113354

The running sum would work, but shouldn't be necessary. The posted formula should work, and I just successfully tested something similar.

[ssanfu]

Now I remember ..... I had problems with something like this last year. I ended up putting the IIF() calc in the query, then using the query column name on the report and summing in the footer. (Unless I'm stuck on stoopid again)

I hate it when I have to keep relearning things.


Thanks Paul.

[RogerC]

Thank you, it worked! I created a new text box and reentered the formula and it worked flawlessly. Appreciate your input.

[pbaldy]

No problemo!