Microsoft Office Tutorials and References

In Depth Information

**Writing Expressions in Access**

Table 2-1

Operators in Order of Precedence

Operator

Purpose

Example

Grouping

( )

(2+2)*5
returns
20
.

^

Exponentiation (raising a

number to a specified power)

5^2
returns
25
.

Multiplication, division

* /

5*6/3
returns
10
.

+ –

Addition, subtraction

6+6–2
returns
10
.

&

String concatenation (connect-

ing chunks of text)

“Hello” & “There”

returns
HelloThere
.

The order or precedence that operators follow can be a real gotcha if you’re

not careful. Take a look at the following simple expression, which includes

an addition operator (+) and a multiplication operator (*):

5+3*2

When you do the math, do you get 16 — or do you get 11? If you do the

addition first (5 + 3 = 8) and then the multiplication (2 * 8), you end up with 16.

But if you do the multiplication first (3 * 2 = 6) and then the addition (6 + 5),

you end up with 11. So which is the correct answer: 11 or 16?

Book III

Chapter 2

Give up? The correct answer (and the one Access comes up with) is 11,

because the order-of-precedence rules state that multiplication and division

are always performed before addition or subtraction.

Multiplication and division are performed in order of precedence. If an

expression involves both of those operations, they’re executed in left-to-

right order. In the following expression, the division operation takes place

first because it’s to the left of the multiplication operation:

10/5*3

The result of the preceding expression is 6, because 10 divided by 5 is 2, and

2 times 3 equals 6.

Addition and subtraction work the same way. If an expression includes both

addition and subtraction, the calculations take place in left-to-right order.

You can control the order of precedence by using parentheses. Access

always works from the innermost parentheses to the outermost, as in the

following example expression:

5^2+((5-1)*3)