iif Statement not working when there is a zero vlaue

[MNNovice]

Hi,
My report (rBalance) is based on a query (q3). q3 is based on two other queries (q1) & (q2)

q1 has two figures: Budget amount and Draw amount
q2 has one figure: Authorized amount. This is a calculated field. Budget amount X Ratio %

To get to balance amount. i am using Authorized Amount - Draw Amount.

Everything works except when there is not Draw Amount.

I tried, "iif(([AuthorizedAmount]-[DrawAmount]) <> 0, [AuthorizedAmount], ([AuthorizedAmount-[DrawAmount]))

I know this should be simple but I just couldn't get it to work. Please help. Thanks.

[twinnyfo]

Is draw amount actually 0 (zero) or is it Null. There is a huge difference between the two....

If DrawAmount is Null (even if it is not), you can use the following:

[AuthorizedAmount] - Nz([DrawAmount], 0)

Hope this helps.

[MNNovice]

Thanks. But it didn't work either. Here is the actual expression I used.

Balance: IIf([FTAAmount]-Nz([SumOfiAmountECHO],0),[FTAAmount],([FTAAmount]-[SumOfiAmountECHO]))

The balance comes out okay where there is no value for SumOfiAmountECHO. But if I have a value for this field the balance is incorrectly showing up as FTAAmount instead of FTAAmount - SumOfiAmountECHO.

Thanks for your time.

[twinnyfo]

There should be no need for the IIf statement. The Nz() function precludes a need for it.

[MNNovice]

Got it. Thank you. Much appreciated.