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(<value>, <true_part>, <false_part>)
Parameters
<value>
Type: Boolean
Boolean value.
<true_part>
Type: None
Value of any type, to be returned if <value> is True.
<false_part>
Type: None
Value of any type, to be returned if <value> is False.
(return)
Type: None
Either <true_part> or <false_part>.
Notes
If <value> is True, the function returns the value of <true_part>. Otherwise, it returns the value of <false_part>.
If the current data value is NULL, <value> is False and the return value is <false_part>. <true_part> or <false_part> (or both) can 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 following 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 <true_part> and the <false_part> 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)
This example intends to avoid raising an error by performing a division by zero; however, when b is zero, the error occurs. This is because the code is 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