Returns one of two parts, depending on the evaluation of an expression.
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 function. See the example table that follows.
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.
For more information about creating queries and calculated fields, see the article Create a simple select query.
Use IIf in VBA code
Note: Examples that follow demonstrate the use of this function in a Visual Basic for Applications (VBA) module. For more information about working with VBA, select Developer Reference in the drop-down list next to Search and enter one or more terms in the search box.
This example uses the IIf function to evaluate the TestMe parameter of the CheckIt procedure and returns the word "Large" if the amount is greater than 1000; otherwise, it returns the word "Small".
Function CheckIt (TestMe As Integer) CheckIt = IIf(TestMe > 1000, "Large", "Small")End Function
More examples
|
Expression |
Results |
|
=IIf([AirportCode]="ORD","Chicago",IIf([AirportCode]="ATL","Atlanta",IIf([AirportCode]="SEA","Seattle","Other"))) |
If [AirportCode] is "ORD", return "Chicago". Otherwise, if [AirportCode] is "ATL", return "Atlanta". Otherwise, if [AirportCode] is "SEA", return "Seattle". Otherwise, return "Other". |
|
=IIf([ShipDate]<Date(),"Shipped",IIf([ShipDate]=Date(),"Shipping today","Unshipped")) |
If [ShipDate] is prior to today's date, return "Shipped". Otherwise, if [ShipDate] equals today's date, return "Shipping today". Otherwise, return "Unshipped." |
|
=IIf([PurchaseDate]<#1/1/2008#,"Old","New") |
If [PurchaseDate] is prior to 1/1/2008, return "Old". Otherwise, return "New." |
|
=IIf(Eval([Volts] Between 12 And 15 And [Amps] Between 0.25 And 0.3),"OK","Out of calibration") |
If [Volts] is between 12 and 15 and [Amps] is between 0.25 and 0.3, return "OK". Otherwise, return "Out of calibration." |
|
=IIf(Eval([CountryRegion] In ("Canada","USA","Mexico")),"North America","Other") |
If [CountryRegion] is "Canada", "USA", or "Mexico", return "North America". Otherwise, return "Other". |
|
=IIf([Average]>=90,"A",IIf([Average]>=80,"B",IIf([Average]>=70,"C",IIf([Average]>=60,"D","F")))) |
If [Average] is 90 or greater, return "A". Otherwise, if [Average] is 80 or greater, return "B". Otherwise, if [Average] is 70 or greater, return "C". Otherwise, if [Average] is 60 or greater, return "D". Otherwise, return "F". |
Note: If you are using the IIf function to create a calculated field in a query, replace the equal sign (=) with a field alias and a colon (:). For example, Status: IIf([ShipDate]<Date(),"Shipped",IIf([ShipDate]=Date(),"Shipping today","Unshipped"))
