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.”