Microsoft Office Tutorials and References
In Depth Information
Writing Expressions in Access
When it’s faced with this expression, Access goes inside the innermost
parentheses first (5–1) and does that calculation. So the expression (for an
instant) becomes
5^2+(4*3)
Next, Access calculates the remaining pair of parentheses in the expression
(4*3). For a moment, the expression becomes
5^2+12
Because no more parentheses are left, Access uses the regular order of
precedence to do the rest of the calculation. Exponentiation has a higher order
of precedence than addition, so for an instant, the expression becomes
25+12
Then Access does the final math and returns the result: 37.
If you’re a real math-head, you’ll appreciate the fact that two more
operators have the same order of precedence as multiplication and division. The
\ operator returns only the integer portion of a quotient, and the MOD (for
modulo) operator returns only the remainder after division. Although 16/3
(normal division) returns 5.3333, 16\3 returns 5, and 16 MOD 3 returns 1.
Using field names in expressions
If you’re thinking, “Big deal — I could have done those preceding
calculations on my \$2 calculator,” that’s certainly true. Access expressions aren’t
limited to numbers and operators, however. You can use field names in
expressions to perform math on data stored in fields. The sample query
shown at the start of this chapter uses the field names [Qty]*[Unit
Price] to multiply the value in the Unit Price field by the value in the
Qty field.
Technically, you need to enclose field names in square brackets only when
the field name contains a blank space, as in [Unit Price]. But you can put
square brackets around any field name, just in case (so to speak). For the
sake of consistency — and to make the field names in expressions stand
out — we always put them in square brackets throughout this topic.
The sample expression shown in the query at the start of this chapter,
[Qty]*[Unit Price], is a prime example of using field names in
expressions. The expression, in English, simply means “the contents of the Qty
field in this record times the contents of the Unit Price field in this same
record.”              Search JabSto ::

Custom Search