Microsoft Office Tutorials and References
In Depth Information
Writing Decision-Making Expressions
✦ StateProv: A regular Text field from the underlying Address Book
table, representing the state to which the order is being shipped.
✦ Qty: A regular Number field from the Order Details table, representing
the quantity of items ordered.
✦ Unit Price: A regular Currency field from the Order Details table,
representing the unit price of the item ordered.
✦ ExtPrice: A calculated field, ExtPrice: [Qty]*[Unit Price],
that multiplies the contents of the Qty field by the contents of the Unit
✦ Sales Tax Rate: A calculated field, SalesTaxRate:
IIf([StateProv]=“NY”,0.0725,0), meaning “If the StateProv
field contains NY, put 0.0725 in this field. Otherwise, put 0 in this field.”
✦ SalesTaxAmt: A calculated field, SalesTaxAmt: CCur([SalesTaxRa
te]*[ExtPrice]), that multiplies the extended price by the sales tax
rate. The CCur() function makes the result appear in Currency format
rather than as a General number.
✦ TotalWithTax: A calculated field, TotalWithTax:
[ExtPrice]+[SalesTaxAmt], that adds the extended price to the
sales tax amount.
Figure 2-11 shows the result of the query. Records that have NY in the
StateProv field show a sales tax rate of 7.25 percent (0.0725). Records
that don’t have NY in the StateProv field show 0 as the sales tax rate. The
SalesTaxAmt and TotalWithTax fields show the results of adding sales
tax. (Because the SalesTaxRate value is 0 outside NY, those records end
up getting no sales tax added to them.)