Creating Queries in Access 2010

  • 6/15/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




Subtraction (6-4=2)


Multiplication (6*4=24)


Division (6/4=1.5)


Integer division (6\4=1)


Addition (6+4=10)


Modular division (6 Mod 4=2)

Comparison Operators




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




Both elements of an expression must be true.


The expression must evaluate as false.


At least one element of an expression must be true.


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