Microsoft Office Tutorials and References
In Depth Information
Writing Decision-Making Expressions
In this function,
✦ conditionalExpression is an expression that results in a True or
✦ doThis is what the function returns if the conditionalExpression
proves to be True.
✦ elseDoThis is what the function returns if the conditionalExpression
proves to be False.
The value of the IIf() function lies in its capability to make a decision
about what to return based on the current situation. Suppose that your
business requires charging 7.25 percent sales tax to New York residents and no
sales tax to everyone else. The State field in the underlying table contains
the state to which the order is shipped. The following expression says, “If
the State field contains NY, return 7.25%; otherwise, return 0”:
Note: In the preceding expression, 0.0725 is just a way of expressing 7.25
percent as a regular decimal number (remove the % sign and shift the
decimal point two places to the left).
Another example of an IIf() function occurs when a Paid field in a table is
a Yes/No field. A Yes/No field can contain only either a True or False value.
The field name alone is a sufficient conditional expression for an IIf()
function, as in the following sample expression:
In English, the expression says, “If the Paid field contains True (or Yes),
return the word Receipt. Otherwise, (if the Paid field contains False)
return the word Invoice.”
Making comparisons in IIf()
Access offers several comparison operators that you can use to define
expressions that result in the True or False values. Selections for these
operators appear in Expression Builder when you choose Operators ➪
Comparison. Table 2-7 describes the Access comparison operators.
The following example IIf() function uses the >= comparison operator to
make a decision based on the contents of a field named Qty:
In English, the expression says, “If the Qty field contains a value greater than
or equal to 10, return Discount. Otherwise, return No Discount.”