IIf
Returns the value passed as the TruePart parameter if the expression evaluates to True, otherwise returns the value passed as the FalsePart parameter.
Syntax
IIf(Val, TruePart, FalsePart)
Parameters
Val
Type: Boolean
Boolean value.
TruePart
Type: None
Value of any type, to be returned if Val is True.
FalsePart
Type: None
Value of any type, to be returned if Val is False.
(return)
Type: None
Either TruePart or FalsePart.
Remarks
If Val is True, the function returns the value of TruePart. Otherwise, it returns the value of FalsePart.
If the current data value is NULL, Val is False and the return value is FalsePart. TruePart or FalsePart (or both) may be NULL.
Examples
This example uses IIf to test the value of the visits variable and return the text “Frequent visitor” if it has a value greater than 5 and a blank string otherwise:
IIf(visits > 5, "Frequent visitor", "")
The next example uses IIf in a data manipulation statement that is being used to clean “dirty” case data. The WHERE clause restricts the data manipulation to cases for which more than one response has been selected for a particular question. The
Rev function is used to return {0} and {1} in alternating calls. Depending on the result,
SortAsc and
SortDesc are called alternately to return the first or last response in the list of responses. Note that the
AnswerCount function is used to test whether more than one response was selected.
UPDATE vdata
SET rating_ent[{Fossils}].Column = IIf(Rev({0,1}, 1, 2) = {0},
rating_ent[{Fossils}].Column.SortAsc(1),
rating_ent[{Fossils}].Column.SortDesc(1))
WHERE rating_ent[{Fossils}].Column.AnswerCount() > 1
Usage considerations
The following behavior should be kept in mind when using the IIF function:
Both TruePart and the FalsePart are evaluated, regardless of which one is actually returned. For example:
value = 10
result = IIf(value = 10, TrueFunction, FalseFunction)
Although TrueFunction is the function intended to be called, IIf will cause both TrueFunction and FalseFunction to be run.
a = 10
b = 0
result = IIf(b <> 0, a / b, 0)
While the above example intends to avoid raising an error by performing a division by zero, whenever b is zero, the error will actually happen. This is due to the fact that the code in the sample will be read as:
a = 10
b = 0
_temp1 = a / b ' Error if b = 0
_temp2 = 0
_temp3 = b <> 0
result = IIf(_temp3, _temp1 , _temp2)
This issue makes the IIf() call less useful than the conditional operator.
See also