Automated change query calc after limit reach?

[MaddiMond]

I am trying to find out how a calculation can automatically be changed in a query after a certain limit, such as 2 Million, has been reached.
At the moment, I have a calculation that takes an amount and multiplies it with 0.01, after 2 Million of the "amount" has been reached, the calculation should automatically change to include multiplication with 0.015 (15%). I have never done this before and don't know where to start. Thanks.

Greetings,

Maddi

 

[Remou]

Hi
Do you mean something like this(?), which can be typed into a query column:

IIf([Amt]+[Amt]*0.01<2000000,[Amt]+[Amt]*0.01,[Amt]+[Amt]*0.015)

 

[MaddiMond]

Yes, I am looking for something like your code. But I only used the IIf in an expression of a control before, never in a query. I tried to put the following code into the criteria line of my TotalMoCommEarned field in the query, but it does not work.

IIf([Equipment$]<2000000,[TotalMoCommEarned]=[Equipment$]*0.01,[TotalMoCommEarned]=[Equipment$]*0.015)

I would like to put it in a query and not in a control, because it seems more stable. What would I have to change to make it work in a query?

Does this criteria line affect the whole query?

Thanks,

Maddi

 

[traingamer]

Don't put it in the criteria. Put it in another column as an expression like

TotComm: IIf([Equipment$] < 2000000,[Equipment$]* 0.01,[Equipment$] * 0.015)

(And (that's 1 percent and 1.5 percent) If you really want 10 and 15 percent, multiply by .1 and .15 instead.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill