set max and min calculated value

[aramas]

how do i take an expresssion such as

PROFIT MARGIN SCORE (200): ([PROFIT MARGIN (15%)]-0.15)*5

and make it so if it is less than zero it is equal to zero and if it is greater than 200 it is equal to 200?

 

[kherm]

I have done this before, but I know there are some good programmers around here.

Expr1: IIf([ProfMarg]<0,0,IIf([ProfMarg]>200,200,[ProfMarg]))

Ken

 

[aramas]

sorry i am a newbie

sorry so do i replace the prviosu expression PROFIT MARGIN SCORE (200): ([PROFIT MARGIN (15%)]-0.15)*5

with this new one or create a new variable with that expression?

 

[kherm]

sorry so do i replace the prviosu expression PROFIT MARGIN SCORE (200): ([PROFIT MARGIN (15%)]-0.15)*5

with this new one or create a new variable with that expression?

In my example, Expr1 will be the query field label (I wasn't sure if PROFIT MARGIN SCORE (200) is a field label, so I used my own example). In my example, ProfMarg stands in for the calculation that must be in the range of zero to 200.

It's the IIf statement that first tests for a less than zero condition and would return 0 if it was, followed by a nested IIf (if the calculation is indeed 0 or greater) and test for a value over 200 and return 200 if it is, otherwise the calculation will not be changed.

Hope that's not too much carrying coals to Newcastle. I wasn't sure what environment you were doing your calculation. I tested mine in a query.

Ken