IIF is a great function in Access that can be used almost anywhere and provides you with capabilities in your code you may not be using. Many will argue it can make your code harder to read, (it does), this post is not here to debate that, but rather provide you examples of it’s flexibility.
First, the definition of what it does:
IIF(Condition, Value_If_True, Value_If_False)
The function will return the “Value_If_True” portion if Condition is True, otherwise it will return “Value_If_False”.
Variable Assignment
You may use it in place of an IF block, for example when assigning a value to a variable:
strOutcome = IIF(Date() = #10/1/09#, "Good", "Bad")
In the above example, if Date() returns 10/1/09 as today’s date then the variable strOutcome will be assigned the value “Good”, otherwise it will get assigned “Bad”.
Building Strings
You can also use it when building strings in your code:
strSQL = "Select * from tblEvents Where User = '" & IIF(strUser = "Juan", "Admin", strUser) & "' Order By User"
If the variable strUser is Juan, the string will look like this:
Select * from tblEvents Where User = 'Admin' Order By User
Otherwise, if its anything else, for example “Joe”:
Select * from tblEvents Where User = 'Joe' Order By User
Power tip: Think of the true or false options as areas to insert code. You can perform complex assignments in both regions:
strSQL = "Select " & IIF(strField = "User", "User: '" & strUser & "'", "") & " From tblEvents"
Notice the code in the True portion: “User: ‘” & strUser & “‘”, it’s building the string inside another string!
Invoking Commands
Here’s a great way to use IIF when invoking a command:
DoCmd.OpenForm "frmEvents",,,,,IIF(strUser = "Juan", acWindowNormal, acDialog)
If the user is “Juan” the form will open in a normal window, otherwise it will open as a dialog.
To see how IIF can be used in a query, click here.
