Use the IIf function to test conditions in a query or expression

The IIf function has the following syntax:

IIf(logical test, value if true, value if false)

For example, to calculate sales tax in a text box on a form, you might enter the following expression in the Control Source property for the text box:

=IIf([StateProvince] = "WA",[OrderTotal] * 0.095, 0)

Translation: if the StateProvince field contains "WA", calculate the sales tax as OrderTotal times 0.095. Otherwise, just return a zero.

Nested IIfs

Once you get the hang of the IIf function, you'll find that nesting them can be very powerful. "Nesting" is when you use an IIf function inside another IIf function, either as the "value if true" or the "value if false" argument (or both). For example, suppose you want a Status column on a report to display "Past Due" if the Due Date has passed for an item. If the Due Date is today, you want to display "Due today". If the Due Date hasn't arrived yet, you want the Status column to be blank. Assuming today's date is February 9, 2012, you'd want to see something like this:

This can be accomplished by using two IIf functions, one nested as the "value if false" argument of the other:

=IIf([DueDate]<Date(),"PAST DUE",IIf([DueDate]=Date(),"Due today"))

The outer IIf compares the DueDate field to today's date (which is returned by the Date() function). For the first two tasks in the report shown above, the due date comes before today's date, so the outer IIf's logical test evaluates to True and it displays "PAST DUE". For the third task (Paint garage), the outer IIF evaluates to False so the nested IIf function tests to see if the due date is equal to today's date. It is, so the nested IIf displays "Due today". For the fourth task (Trim trees), neither of the IIf functions evaluates to "True", so nothing displays because there's no "value if false" argument for the nested IIf. Of course, you could add an argument if you wanted something to display in this case.

A word to the wise

Nesting IIf functions is fun, but you generally don't want to go more than one or two levels deep. The expressions can quickly become hard to read and maintain. If your nested IIfs are getting out of hand, you might consider using the Switch function, which provides a similar "if – then" kind of logic.

Top of Page