expression builder Iif not recognising text

[CJ81]

Can anyone tell me what's wrong with this iif statement?

=IIf([Details]="COST OF PRODUCT",[PerUnit]*[Quantity],0)

it keeps coming back with 0 even when the text says COST OF PRODUCT

I have also tried:
=IIf([Details]="COST OF PRODUCT*",[PerUnit]*[Quantity],0)
=IIf([Details] Like "COST OF PRODUCT",[PerUnit]*[Quantity],0) =IIf([Details] Like "COST OF PRODUCT*",[PerUnit]*[Quantity],0)

I'm using the expression builder to create it in a total box in the report footer, I have a box that gives me the total of the report using a sum and I need to show the cost of product separately to this as I need to use it in another calculation.

Thanks
Carrie

 

[marlan]

Hi,

Try putting it in a SUM function:
=SUM(IIF(...))/

Otherwise: try a text box on each record in the details section, value =Nz([PerUnit],0*[Quantity]), have it's Visible=False
and have the control in the footer sum it:
=SUM(txt).

ATB

 

[CJ81]

That works perfectly:
=Sum(IIf([Details]="COST OF PRODUCT",[PerUnit]*[Quantity],0))
Thank you

 

[marlan]

Great!

=Nz([PerUnit],0*[Quantity])

should have bin:
=Nz([PerUnit],0)*[Quantity]