In Access, is the "falsepart" of the IIf Function required?

user_4692331 0
Viewed 130

While converting some Access queries to T-SQL, I came across the Access function IIf. I read the documentation here and understand it takes 3 parameters: the expression, the if true, and the if false. What I have yet to find is if the "false" param is required? And if a false is not provided, what is the behavior?

UPDATE: When this question was asked, I did not know if the query worked with only 2 parameters (b/c I can not run the query in Access). I did, however, suspect it might work (with only 2 params) b/c a few queries I am converting (to T-SQL) have nested IIF's and the last IIF has only 2 params.

required ms-access
3 Answers

It's IIf, not IFF. All arguments are required in VBA and textbox expressions and both must be calculatable (no error result such as DivBy0) because both parts will be calculated - if either errors, the entire expression errors. The IIf() in Access query works differently - 'falsepart' calculates only if 'truepart' fails and if 'falsepart' is not provided, expression returns Null if 'truepart' fails.

Try some expressions and see what happens. One for testing: IIf(1=2,"T").

user_76071900

Addendum to June7's excellent answer:

Here are two simple queries that work in Access SQL:

SELECT IIf(1=1, "yes")

(returns "yes")

SELECT IsNull(IIf(1=2, "yes"))

(returns -1 aka True) - so if you omit falsepart, IIf() returns indeed NULL if expr doesn't evaluate to True.

user_38202710

Yes, it's required, it's actually like an if else statement.

i.e an action occurs if the condition is fulfilled, and another action occurs if condition is not fulfilled

user_116086790
Related