Challenging IIF in query (1 Viewer)

[ascaife]

Any IIF wiz out there?

Fields are as follows:
AmountPaid
AmountCharged
Status


If [Status] = 201 or 202 and [AmountPaid] >=$500 or <$500 but >= 80% of [AmountCharged], then result is [AmountPaid] x 1.25, else result is [AmountPaid]

Challenging for me at least, I'm sure someone out there can whip this up in a few seconds.

 

[Galaxiom]

Try this, untested:

IIF(([Status]BETWEEN 201 AND 202) AND ([AmountPaid]>=500 OR ([AmountPaid]<500 AND [AmountPaid]>=[AmountCharged]*0.8)), [AmountPaid]*1.25,[AmountPaid])

 

[ascaife]

Thanks for the attempt, unfortunately no go on that one. Just comes up with #Error

 

[Galaxiom]

If this is in a control add an equal sign at the beginning.

 

[ascaife]

Wow you seem to be handling all my queries today. Much appreciated.

No the statement is just in a query.

 

[ascaife]

Just sorted it. Seems I needed " " around 201 and 202

 

[Galaxiom]

Just sorted it. Seems I needed " " around 201 and 202

Didn't realise the field with 201 and 202 was text. Good that you were able to work this out.

Just watch out using Between with text.
"201" is between "20" and "30"