IIF()- invalid syntax error

Question

I keep getting this error when trying to run this IIF statement.

Here's what I have:

IIF([Explanation] Like "*No Alternative*" Or Like "*1*" Or Like "*least costly room*" Or Like "*best possible rate*" Or Like "*best rate available*" Or Like "*best available rate*" Or Like "*least expensive*" Or Like "*cheapest hotel room*" Or Like "*lowest price*" Or Like "*cheapest rate*", “No Alternative”, IIF([Explanation] Like "*Required location*" Or Like "*2*", “Required Location”, IIF([Explanation] Like "*Least total cost*" Or Like "*3*" Or Like "*most economical*", “Least Total Cost”, “No Alternative”)))

Here: Or Like "most economical", “Least Total Cost”, “No Alternative” You have commas there you need to seperate out to more like statements — Brad
– Brad, Commented Mar 30, 2018 at 14:51
"Least Total Cost" is what the output should be if [Explanation] is like the previous string of examples. "No Alternative" is what the output should be if all the three previous IIF conditions were not present. — Katie
– Katie, Commented Mar 30, 2018 at 14:56
You need to state the field name before every like, not just the first. Adjust that. — Erik A
– Erik A, Commented Mar 30, 2018 at 14:56

SunKnight0 · Accepted Answer · 2018-03-30 15:13:33Z

3

Instead of

[Explanation] Like "*No Alternative*" Or Like "*1*"

you need

[Explanation] Like "*No Alternative*" Or [Explanation] Like "*1*"

answered Mar 30, 2018 at 15:13

SunKnight0

3,3611 gold badge13 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScaisEdge · Accepted Answer · 2018-03-30 16:10:28Z

1

you should check for [Explanation] in each OR eg:

IIF( ([Explanation] Like "*No Alternative*" 
Or [Explanation] Like "*1*" 
Or [Explanation] Like "*least costly room*" 
Or [Explanation] Like "*best possible rate*" 
Or [Explanation] Like "*best rate available*" 
Or [Explanation] Like "*best available rate*" 
Or [Explanation] Like "*least expensive*" 
Or [Explanation] Like "*cheapest hotel room*" 
Or [Explanation] Like "*lowest price*" 
Or [Explanation] Like "*cheapest rate*"), "No Alternative", 

  IIF( ([Explanation] Like "*Required location*" 
    Or [Explanation] Like "*2*"), "Required Location", 

      IIF( ([Explanation] Like "*Least total cost*" 
        Or [Explanation] Like "*3*" 
        Or [Explanation] Like "*most economical*"), "Least Total Cost",
             "No Alternative")))

edited Mar 30, 2018 at 16:10

answered Mar 30, 2018 at 15:18

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

4 Comments

Katie Over a year ago

That didn't work for me --both adding [Explanation] before each 'Like' and copying the exact statement above. I still get the same message.

ScaisEdge Over a year ago

are you sure you have not some others part of code that may lead to some problems on IFF ..?? anyway add some () for a better closing of each logical condition ..

ScaisEdge Over a year ago

also changed with regular double quote the true literal expressions

Katie Over a year ago

thank you. I ended up just rewriting it all instead of copying and pasting and it worked.

Collectives™ on Stack Overflow

IIF()- invalid syntax error

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related