IIf function for 3 possible outcomes?

[levi252]

I want to calculate a rate for 3 types of Gears (coded "OT04S", "OT16S", and
"POT02S". I've used IIf in the past when the database contained only 2 Gear
types, but can't seem to get the expression correct now that there are 3 Gear
types. Can I use the IIf function here or should I be going in another
direction?
Here's my best attempt at a 3-outcome IIf:
'Rate:
IIf([Gear]="OT04S",([Count]/([Distance]*"2.93")),(IIf([Gear]="OT16S",([Count]/([Distance]*"3.11"))),([Count]/([Distance]*"1.75"))))'
I get error message "Syntax error (comma) in query expression".
Thanks in advance for help.

 

[Rick Brandt]

I want to calculate a rate for 3 types of Gears (coded "OT04S",
"OT16S", and "POT02S". I've used IIf in the past when the database
contained only 2 Gear types, but can't seem to get the expression
correct now that there are 3 Gear types. Can I use the IIf function
here or should I be going in another direction?
Here's my best attempt at a 3-outcome IIf:
'Rate:
IIf([Gear]="OT04S",([Count]/([Distance]*"2.93")),(IIf([Gear]="OT16S",([Count]/([Distance]*"3.11"))),([Count]/([Distance]*"1.75"))))'
I get error message "Syntax error (comma) in query expression".
Thanks in advance for help.

You can nest IIf(), but as you have seen, looking at the result and trying to
figure out if you have done it correctly can be a pain. In a case like yours I
prefer to use the Switch funtion...

Count/(Distance * Switch([Gear]="OT04S", 2.93, [Gear]="OT16S", 3.11, 1=1, 1.75))

When Switch() gets overly complicated I build a user-defined function. The
logic when building those can utilize line-breaks and indenting to make it easy
to keep straight.

 

[Jeff Boyce]

When I get "Syntax Error" messages and I can't find what it's talking about,
I generally start over, with the simplest condition. Then, step by step, I
build up complexity until the expression is doing what I wanted.

I suspect your third condition is NOT being covered "inside" the second
IIF() expression.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Jamie Collins]

You can nest IIf(), but as you have seen, looking at the result and trying to
figure out if you have done it correctly can be a pain. In a case like yours I
prefer to use the Switch funtion...

When Switch() gets overly complicated I build a user-defined function.

Alternatively, considering this is the 'queries' group, the values can
be put in a table and, well, queried (hint: use a join). This gives
good flexilibity e.g. if you need to add/alter/delete a rate then it
could be as simple as a single INSERT/UPDATE/DELETE statement rather
than a VBA/SQL code change.

Jamie.

--