IIF and Sum on an Access report

RustySpoon

I am trying to get some info on an Access 2000 report that shows me the total value of customers who have paid for a product.
The field which shows paid or not is a checkbox.

I am trying to show:

The number of units - =Count([Tester_ID])
The total € of units ordered - =Sum([price])
The number of units paid for - =Count(IIf([paid]="Yes",0))
The total € of units paid for - =IIf([paid]="Yes",Sum([price]))


This is all coming from a query which first filters by a month.
The only field i cannot populate is the total € of units paid for.
It looks like i am missing a false criteria but if i put
=IIf([paid]="Yes",Sum([price]),0) the result is zero.
I would just multiply units by a value but all the units are not standard price.

Can anyone help me out on this hot day?

stevenmu

I'm not sure if it'll let you do this but I think you want the SUM to be outside the IIF i.e.
SUM(IIF([paid]="Yes",[price],0))

bonkey

Yup. Put the sum outside.

Alternately, put the IIF into the Query, and just SUM the value in the report.

RustySpoon

Thanks for the help, worked a treat

Inspector Gadget

RustySpoon:

In case you didn't spot it, Iif() takes three operators, not two:

ReturnValue = IIf({expression to evaluate as boolean}, {return this if true}, {return this if false})

Gadget