Home > Sample chapters > Microsoft Office > Access

Creating Queries in Access 2010

Introducing Operators

There are several types of database objects and tools you need to use when you create a criterion to narrow the records returned by a query or to calculate a value. The first set of objects to which you need to refer includes database tables and their fields. For example, to calculate the subtotal of a line in the Northwind sample database’s Order Details table, multiply the Unit Price by the Quantity ordered, and adjust the total if the customer gets a discount (as noted in the Discount field). The expression to perform the first part of that calculation is [Order Details]![UnitPrice]*[Order Details]![Quantity].

Note that table fields are called out with the name of the table enclosed in square brackets, an exclamation point, and then the name of the field in square brackets.

Arithmetic Operators

Operator

Description

-

Subtraction (6-4=2)

*

Multiplication (6*4=24)

/

Division (6/4=1.5)

\

Integer division (6\4=1)

+

Addition (6+4=10)

Mod

Modular division (6 Mod 4=2)

Comparison Operators

Operator

Description

<

Less than

<=

Less than or equal to

<>

Not equal to

=

Equal to

>=

Greater than or equal to

>

Greater than

Between “Value1” And “Value2”

Between two values, inclusive (for example, Between “1” And “3” would return “1, 2, 3”)

Logical Operators

Operator

Description

AND

Both elements of an expression must be true.

NOT

The expression must evaluate as false.

OR

At least one element of an expression must be true.

XOR

Exactly one element of an expression must be true, not both.