IIf function for 3 possible outcomes?


L

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.
 
Ad

Advertisements

R

Rick Brandt

levi252 said:
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.
 
J

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/
 
Ad

Advertisements

J

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.

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple IIf's 6
Nested IIf: Define ranges, >= And <=? 2
Busted IIf function 3
IIf statement Query 17
Count a calculated field 6
Help needed with IIF in my query 14
Query IIF function 3
nested iif function 4

Top