Microsoft Access 2010 Bible

Chapter 5: Using Operators and Expressions in Access ...............................................................

173

There are seven basic mathematical operators:

+ Addition

• Subtraction

• Multiplication
  / Division
  \ Integer division
  ^ Exponentiation
  Mod Modulo

I cover each of these operators in detail in the following sections.

The addition operator: +

If you want to create a calculated field in a query for adding the value of tax to the price, use an

expression similar to (calculated fields are covered in detail in Chapter 18):

[TaxAmt]+[tblSalesLineItems].[Price]

To use this expression, you would have to create a calculated field in the query named [TaxAmt]

using the multiplication operator:

TaxAmt: [tblSales].[TaxRate] * [tblSalesLineItems].[Price]

You could also create a form for adding the values, such as GrossAmount and Tax, in which case

you would use [GrossAmount] + [Tax]. This simple expression uses the addition operator to

add the contents of both fields and display the result in the object containing the expression.

Note
In this example, specifying the table name isn’t necessary because your tables have only one field named
FirstName and one field named LastName. However, it’s good practice to specify the table containing the
field, using a period as the separator.

Caution
Although you can concatenate (join) text strings by using the addition operator, you should use the ampersand
(&) operator to avoid confusing Access. When you use the plus sign (+), Access initially interprets the operands as
numeric values and only switches behavior when it determines that the operands are strings. You can very easily
introduce side-effect bugs into an application when using overloaded operators like the plus sign. You can read
more about concatenation and other operations in the “String operators” section, later in this chapter.

The subtraction operator: –

The minus sign (–) performs simple subtraction, such as calculating a final invoice amount by sub-

tracting a discount from the price:

[Price] - ([Price] * [DiscountPercent])