Access - Report: Conditional summary fields

Question

I have a report which shows details and a footer, which has summary ("sum[MyFieldname]") field. 

What I want is, that the sum is not calculated over all single records but only those which meet a certain criteria. E.g. I have a boolean field ("MyBoolean") and the sum should exclude the records whre that field is False. Something like "give me the sum of all records excluded the ones where the "MyBoolean" field is false".

I would be happy if someone could give me a hint how to do that.

Chris

Answer 1

A more general solution is to use an IIF statement, especially for fields that are not boolean or if you want a value other than SUM such as Count or Average.

= Sum(IIF([MyTestField]=True,[MyFieldToSum],Null))

= Sum(IIF([MyTEXTfield]="Virginia",[MyFieldToSum],Null))

---

John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County

Answer 2

Hello Hans,

that is a really tricky approach ;)

Answer 3

You can use

=-Sum([MyFieldName]*[MyBoolean])

The Boolean value True is equivalent to -1 and False to 0, so multiplying MyFieldName with MyBoolean results in -MyFieldName if MyBoolean is True and in 0 otherwise. The - before the Sum corrects the final result.