You can use IIf anywhere you can use expressions. You use IIf to determine if another expression is true or false. If the expression is true, IIf returns one value; if it is false, IIf returns another. You specify the values IIf returns.
Syntax
IIf ( expr , truepart , falsepart )
The IIf function syntax has these arguments:
|
Argument |
Description |
|---|---|
|
expr |
Required. Expression you want to evaluate. |
|
truepart |
Required. Value or expression returned if expr is True. |
|
falsepart |
Required. Value or expression returned if expr is False. |
Remarks
IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True.
Examples
Use IIf on a form or report Suppose you have a Customers table that contains a field named CountryRegion. In a form, you want to denote whether Italian is the first language of the contact. You can add a control and use IIf in its Control Source property, like so:
=IIf([CountryRegion]="Italy", "Italian", "Some other language")
When you open the form in Form view, the control displays "Italian" whenever the value for CountryRegion is Italy, and "Some other language" whenever CountryRegion is any other value.
Use IIf in complex expressions You can use any expression as any part of an IIf statement. You can also "nest" IIf expressions, allowing you to evaluate a series of dependent expressions. To continue with the preceding example, you might want to test for several different CountryRegion values, and then display the appropriate language depending on which value exists:
=IIf([CountryRegion]="Italy", "Italian", IIf([CountryRegion]="France", "French", IIf([CountryRegion]="Germany", "German", "Some other language")))
The text "Some other language" is the falsepart argument of the innermost IIf function. Since each nested IIf function is the falsepart argument of the IIf function that contains it, the text "Some other language" is only returned if all the expr arguments of all the IIf functions evaluate to False.
For another example, suppose you work at a library. The library database has a table named Check Outs that contains a field, named Due Date, that contains the date a particular book is due back. You can create a form that indicates the status of a checked out item in a control by using the IIf function in that control’s Control Source property, like so:
=IIf([Due Date]<Date(),"OVERDUE",IIf([Due Date]=Date(),"Due today","Not Yet Due"))
When you open the form in Form view, the control displays "OVERDUE" if the value of Due Date is less than the current date, "Due today" if it is equal to the current date, and "Not Yet Due" otherwise.
Note: To use logical operators such as "And" or "Or" in the expr argument of the IIf function, you must enclose the logical expression in the Eval Links to an external site. function.
Use IIf in a query
The IIf function is frequently used to create calculated fields in queries. The syntax is the same, with the exception that in a query, you must preface the expression with a field alias and a colon (:) instead of an equal sign (=). To use the preceding example, you would type the following in the Field row of the query design grid:
Language: IIf([CountryRegion]="Italy", "Italian", "Some other language")
In this case, "Language:" is the field alias.