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 | 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.
See Also
Choose function, If...Then...Else statement, Select Case statement, Switch function.
Specifics (Microsoft Access)
You can also use the IIf function (immediate if) in a calculated control on a Microsoft Access 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 force 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, and returns an empty string if it is Null.
=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. In 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 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")
End Function
Example (Microsoft Access)
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")