IIf Function

Returns one of two parts, depending on the evaluation of an expression.

You can use IIf anywhere that you can use expressions. Use IIf to determine whether another expression is true or false. If the expression is true, IIf returns one value. If it's false, IIf returns another value. You specify the values that IIf returns.

See some examples

Syntax

IIf(expr, truepart, falsepart)

The IIf function syntax has these arguments:

Argument	Description
expr	Required. Expression that 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 behavior, watch for unwanted 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 that you have a Customers table that contains a field named CountryRegion. In a form, you want to show whether Italian is the first language of the contact. You can add a control and use IIf in its Control Source property:

=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. It displays Some other language whenever CountryRegion has any other value.

Use IIf in complex expressions

You can use any expression in any part of an IIf statement. You can also nest IIf expressions, which lets you evaluate a series of dependent expressions. To continue with the preceding example, you might want to test several different CountryRegion values and then display the appropriate language based on the value:

=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 returned only if all the expr arguments of the IIf functions evaluate to False.

For another example, suppose that you work at a library. The library database has a table named Check Outs that contains a field named Due Date, which contains the date that a particular book is due back. You can create a form that shows the status of a checked-out item in a control by using the IIf function in that control's Control Source property:

=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.

Use IIf in a query

The IIf function is often used to create calculated fields in queries. The syntax is the same, except 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, 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 Create a simple select query.

Use IIf in VBA code

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

1. Expression:

   =IIf([AirportCode]="ORD","Chicago",IIf([AirportCode]="ATL","Atlanta",IIf([AirportCode]="SEA","Seattle","Other")))
   

   Result: If [AirportCode] is "ORD", return "Chicago". Otherwise, if [AirportCode] is "ATL", return "Atlanta". Otherwise, if [AirportCode] is "SEA", return "Seattle". Otherwise, return "Other".

2. Expression:

   =IIf([ShipDate]<Date(),"Shipped",IIf([ShipDate]=Date(),"Shipping today","Unshipped"))
   

   Result: If [ShipDate] is earlier than today's date, return "Shipped". Otherwise, if [ShipDate] equals today's date, return "Shipping today". Otherwise, return "Unshipped".

3. Expression:

   =IIf([PurchaseDate]<#1/1/2008#,"Old","New")
   

   Result: If [PurchaseDate] is earlier than 1/1/2008, return "Old". Otherwise, return "New".

4. Expression:

   =IIf(Eval([Volts] Between 12 And 15 And [Amps] Between 0.25 And 0.3),"OK","Out of calibration")
   

   Result: If [Volts] is between 12 and 15 and [Amps] is between 0.25 and 0.3, return "OK". Otherwise, return "Out of calibration".

5. Expression:

   =IIf(Eval([CountryRegion] In ("Canada","USA","Mexico")),"North America","Other")
   

   Result: If [CountryRegion] is "Canada", "USA", or "Mexico", return "North America". Otherwise, return "Other".

6. Expression:

   =IIf([Average]>=90,"A",IIf([Average]>=80,"B",IIf([Average]>=70,"C",IIf([Average]>=60,"D","F"))))
   

   Result: 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".