Microsoft Office Tutorials and References
In Depth Information
Writing Decision-Making Expressions
The conditional expression, [Last Name]=“Pines” And [First
Name]=“Tori” means “if the Last Name field contains Pines and the
First Name field contains Tori.” One condition is that the Last Name
field contain Pines; the other condition is that the First Name field
contain Tori. If both those conditions are True, the expression returns No
Charge. If either condition is False, or if both conditions are False, the
expression returns Charge.
Another example of the Or operator is the following expression:
IIf([State]=”NY” Or [State]=”NJ”,”Tax”,”No Tax”)
In the preceding example, the first condition is that the State field contains
NY. The second condition is that the State field contains NJ. The Or
operator says that either or both of the conditions must be met for the whole
conditional expression to return True. If the State field contains NY or NJ, the
expression returns Tax. If the State field contains anything other than NY
or NJ, the expression returns No Tax.
To tax or not to tax?
A practical example of using an IIf() function in calculated field
expressions is whether to tax. Suppose that you have a query like the one shown
in Figure 2-10. Your business requires that you charge 7.25 percent tax to
all orders shipped within the state of New York. You charge no sales tax on
orders shipped outside New York. The StateProv field in the query
contains the state to which the order is shipped.
Obviously, you can’t see all the expressions in the query; the QBE grid isn’t
wide enough on many monitors to show them. You can expand the width of
individual columns to see that column’s full expression. Following is a quick
summary of what the fields in the query represent: