I am quite new to MS Access (and databases in generally) but I have noticed that IF() statements are actually written using the IIF() function.

A quick search online told me that it stands for Immediate If, I assume because it is evaluated immediately.

Support.Office.com tells me that IIF() has the same functionality I am used to from IF() in other programs and languages, so my question is why is it IIF() and not IF()?

I am unable to find this answer anywhere.

Is there a "delayed" IF (Or any other kind of IF) that justifies the need for Immediate IF? If so, what are the possible use cases for the other if?

If there is not a different IF, why is the IIF() function not just called IF()?


1 Answer 1


Within Access, IIf ( expr , truepart , falsepart ) is a function with three parameters.

Also available in Access is VBA (Visual Basic for Applications) and it has the usual If ... Then ... ElseIf ... End If conditional execution blocks, but in that case If and the other terms are all keywords of the Basic language.

The functional form IIF() can be used within SQL statements or as part of single executable statements within certain object properties, e.g. Control Source properties of form controls. In both of those cases, a complete multi-line If construct (i.e. a full VBA code block) is NOT allowed. Without the functional form, there would be no way to execute conditional expressions in such contexts.

One reason for the different names is to avoid the conflict of having a function with the same name as a keyword. It is interesting to note that Excel also supports VBA, but it still has a spreadsheet function named IF(). Although some of the reasons are perhaps just convention (style) decided by the original application designers, there are likely other more subtle reasons. For instance, Access is more likely to have mixed expressions in different contexts (as mentioned earlier), so it is important to avoid conflicts with VBA keywords. On the contrary, it is not possible to execute Excel "spreadsheet commands" in any other context outside spreadsheet cells. Although it is possible to execute properly-defined VBA functions from an Excel cell, one cannot execute VBA code directly from within the spreadsheet commands. In other words, the execution contexts are completely separate in Excel, so there is never a conflict between function names and language keywords.

In the case of Access, the concept of "immediate" or "delayed" versions of If are not relevant. Some languages (Java, C++, C#, etc.) support short-circuited logical operators (like && -- logical and) which only execute a 2nd statement when logically necessary. But neither the IIF() function nor the VBA If statement support that behavior. All expressions and/or function calls that are "passed" to either form will be fully evaluated/executed. In some contexts, like within an SQL statement, such expressions might evaluate to NULL and so the overall expression might also be NULL and effectively ignored, but ignoring the result of the expression is not the same as skipping the execution of individual statements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.