Go to Top

Using IIF examples and tips

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.

MS Access Report Generator

First, the definition of what it does:

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:

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:

If the variable strUser is Juan, the string will look like this:

Otherwise, if its anything else, for example “Joe”:

Power tip: Think of the true or false options as areas to insert code. You can perform complex assignments in both regions:

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:

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.

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

Leave a Reply

Your email address will not be published. Required fields are marked *


Contact Us
close slider
  • This field is for validation purposes and should be left unchanged.