another calculation problem

[stryder09]

I want to enter a value and it will have 3 different solutions. basically if [field1]>150 put the value 20, if [field1]>100 put the value 5, if [field1]>50 put the value 2.



I thought maybe something like this but it doesnt work.

=IFF([field1]>150,20,0) or IFF([field1]>100,5,0) or IFF([field1]>50,2,0)

[RuralGuy]

You do understand that say 175 meets *all* of the criteria, right?

[stryder09]

I guess in essence what I need it to do is if it is between 51 and 100 assign a value of 2 if it is between 101 and 150 assign a value of 5 and if it is above 150 assign a value of 20 so 175 doesn't meet the criteria because then unless above 175 if would always show 0. It is a demerit system that I am creating.

[RuralGuy]

< 51 = 0 right?

[stryder09]

yes that is correct

[RuralGuy]

Something like:
=IFF([field1]<51,0,IFF([field1]<101,2,IFF([field1]<151,5,20)))
AIR CODE so I may have the syntax wrong.

[stryder09]

I keep getting back the error #Name?

[RuralGuy]

Are you puting this in a form or a query? What is the actual name of [field1]?

[weekend00]

IIF , not IFF

[RuralGuy]

IIF , not IFF

Thanks! I seem to always make that mistake even though I know it stands for Immediate IF.

[weekend00]

Thanks! I seem to always make that mistake even though I know it stands for Immediate IF.

I didn't know it stands for Immediate IF and I didn't dig into it.
I just thought Microsoft people double the first character to distinguish from IF when they decide the IIF function. Just like some time I double the first character to make a field name from a reserved name, nName for example.

[RuralGuy]

See how much wiser you are today than yesterday?

[stryder09]

Yes I am wiser today thank you! I was wondering why IIF?? Anyhow, my field name is [actualbagvariance]. Lol and this is only 1of like 30 built equations I need!

[RuralGuy]

Is this in a query or the ControlSource of a control on a form? I would put it in the query if possible.

[stryder09]

It's on a form but I will be putting it in a query as well soy reports outs work.