IIf Function

Description

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

Syntax

IIf(expr, truepart, falsepart)

The IIf function syntax has these named arguments:

Part	Description
expr	Expression you want to evaluate.
truepart	Value or expression returned if expr is True.
falsepart	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.

Specifics (Microsoft Access)

The IIf function (immediate if) is most useful in a calculated control on a form or report.

You can use the IIf function to evaluate an expression and return either of two other values, depending on whether the expression evaluates to True (-1) or False (0). For example, you can use the IIf function to inspect a field on a form and determine whether its value is Null. If it is, you can have the function return an empty string. If the field has a value that is not Null, the function could return the field’s contents. The following example checks the ShipCountry field in an Orders table.

= IIf(IsNull(Forms!Orders![ShipCountry]), "", _
    Forms!Orders![ShipCountry])

In Visual Basic, the IIf function evaluates both truepart and falsepart, even though it returns only one of them. On a Microsoft Access form or report, however, the IIf function evaluates either truepart or falsepart, whichever is appropriate. Therefore, you need not be concerned about the undesirable side effects of evaluating both arguments if you use the IIf function in a calculated control, query expression, or macro.

Note The Microsoft Access Nz function converts Null values to zero, a zero-length string, or another value that you specify. If your expression handles Null values, you may be able use the Nz function as an alternative to the IIf function.

The IIf function is most useful within expressions that are in a form or report, rather than in Visual Basic code in a module. In Visual Basic, the more full-featured If...Then...Else statement offers greater versatility.

Example

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")Function

This example uses the IIf function to evaluate an OrderAmount field and returns the word “Large” if the amount is greater than 1000; otherwise, it returns the word “Small”. You can enter the following expression in the ControlSource property of a calculated control.

= IIf([OrderAmount] > 1000, "Large", "Small")

In the next example, the IIf function returns a zero-length string if the ShipCountry field is Null; otherwise, it returns the field’s contents.

= IIf(IsNull([ShipCountry]), "", [ShipCountry])

The following example shows how you can use the IIf function to return a string containing an employee’s first name, middle initial, and last name. If there is no middle initial, the expression returns the first and last names separated by a single space. If there is a middle initial, however, it includes the middle initial and a period.