Another equation question

[stryder09]

Okay I have to assign a value to a [field2] depending on the number in the [field1] it will give me a rating.

Between 0 and 5.25 it will give me a rating of 20
Between 5.25 and 5.499 it will give me a rating of 5
Between 5.499 and 6.401 it will give me a rating of 0
Between 6.401 and 6.9 it will give me a rating of 5
Anything greater than 6.9 will give me a rating of 20

I put in the equation below and it works and gives me a rating of 20 back if I'm above 6.9 and below 5.25 but it will not give me the rating 5 or 0 between the other ranges.



=IIf([pH2]>0,20,IIf([pH2]>5.25,5,IIf([pH2]>5.499,0,IIf([pH2]>6.401,5,IIf([pH2]>6.9,20,0)))))


Any help would again be much appreciated!

Brad

[pbaldy]

The IIf() will drop out as soon as a test is true, so you'd want to reverse your tests. You might find Switch() easier to work with, or putting the ranges and values in a table, which would be more maintainable in the long run.

[orange]

I agree with Paul
= IIf([x] > 6.9, 20, IIf(x > 6.401, 5, IIf([x] > 5.499, 0, IIf([x] > 5.25, 5, IIf([x] > 0, 20, 0)))))

He's just quicker with the answer.

[stryder09]

Perfect thanks guys.