IIF Query

[linanderson]

Hi can someone help me with this query.

I have a list of bonus amounts[BonusAmt]. What I now want to achieve is calculate an actual bonus sum[ActBonus]. For example. If the bonus amount is a negative number the actual bonus is 0. If it is between 1 and 20 it should be the bonus amount. If it is over 20, it should be 20.

I have started: ActBonus: IIf([BonusAmt]???,[BonusAmt],"0"). No options I have placed in the ??? work.

For info my BonusAmt Query is: BonusAmt: (([TotMths]-20000)/20000)*20. The TotalMths is a sume of 12 months earnings. I figured this should be calculated first rather than combine.

Any help would be appreciated.

 

[vangogh228]

This should work:

ActBonus: IIf([Bonus Amount]<=0,0,IIf([Bonus Amount]>=20,20,[Bonus Amount]))

If you substitute the formula you are using for the [Bonus Amount] field in the calculation, you really won't even need the [Bonus Amount] field.

Hope this helps.

Tom