IIf Function

Returns one of two parts, depending on the evaluation of an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.).

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.

See some examples

Syntax

IIf(expr, truepart, falsepart)

The IIf function syntax has these arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

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 (alias (SQL): An alternative name for a table or field in expressions. Often used to shorten the table or field name for subsequent references in code, to prevent possible ambiguous references, or to provide a more descriptive name in query output.) 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 Select data by using a 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 (alias (SQL): An alternative name for a table or field in expressions. Often used to shorten the table or field name for subsequent references in code, to prevent possible ambiguous references, or to provide a more descriptive name in query output.) and a colon (:). For example, Status: IIf([ShipDate]<Date(),"Shipped",IIf([ShipDate]=Date(),"Shipping today","Unshipped"))

 
 
Applies to:
Access 2013, Access 2010, Access 2007