MS Access —

Working with Expressions and the Expression Builder

The makes it easy to create expressions

You can add calculations to queries, forms, and reports by typing an expression, or a formula that tells Access exactly what to calculate. An expression can be any combination of values, identifiers (such as the value in a field), and operators that result in a value. Here's an example of an expression that calculates profit from two fields called Income and Expenses:

Profit: [Income] - [Expenses]

You can also use constants in an expression, such as:

Commission: [Sales] * .15

Number fields aren't the only types of fields that you can use in you can also perform calculations with dates, times, and text data.

The problem with creating expressions is you have to enter a formula so that Access understands it (and believe me, Access isn't very bright). For example, when you create an expression, some types of information must be enclosed between special characters so that Access knows what type of information it istable names and field names must be enclosed in [brackets], text strings in "quotation marks," and so on. Table has more information about how to use various elements in an expression.

If you know what you want an expression to do but not how to write it, you can try using the Expression Builder. The Expression Builder lets you pick the fields, mathematical symbols, and functions you can use to create an expression.

This lesson will give you some more experience writing expressions both on your own and using the Expression Builder.

  1. Click the qrySales query and then click the Design button.

    First you need to insert a blank column at the beginning of the design grid. Here's how:

  2. Position the mouse over the top of the Normal Price field (until changes to ) and click to select the field. Press Insert to add a new column.

    A column is inserted before the Normal Price column. In the next step, you will learn how to create one of the most common database functions: how to combine the tblEmployees table's FirstName and LastName fields to display the full name.

  3. Type Agent:[FirstName]&" "&[LastName] in the Field row.

    The is used to combine or concatenate two or more text fields. The " " adds a space between the [FirstName] and [LastName] fields.

    Next you need to enter an expression that calculates the cost of the tour by multiplying the Number of Tickets field by the Normal Price field. You will use the Expression Builder to help you write this expression.

  4. Click a new blank field and click the Build button on the toolbar.

    Another way to use the Expression Builder is to right-click in any field and select Build from the shortcut menu.

    The Expression Builder appears, as shown in figure. The Expression Builder contains an area where you can build the expression, buttons you can use to build the expression, and the fields and controls in the current query, report, or form.

  5. Double-click Normal Price, click the Multiplication button, and double-click Number of Tickets.

    The completed expression appears in the expression box, as shown in figure. You're finished writing the expression so you can close the .

  6. Click OK.

    The Expression Builder closes. Add a meaningful label to the new calculated field.

  7. In the new calculated field, replace the Expr1: label with Total:

    The edited expression should read Total:[Normal Price]*[Number of Tickets]. Let's see the results of the new query.

  8. Click the Run button on the toolbar.

    The query combines the FirstName and LastName fields in the new Agent field and displays the total sales price in the new Total field.

  9. Click the Save button on the menu to save your changes and then close the query.

Microsoft Access is very strict about how you write your expressions. If your expressions aren't written in the correct syntax, they won't work. Use the following table as a guideline for adding fields, text, and constants to your expressions.

How Types of Data Should Look in an Expression
Type of Data How It Should Look

Text

"Minneapolis"

Date/Time

#20-Mar-99# (Access will add the # symbols)

Field Name

[Price]

Field Name in a Specific Table

[Products]![Price]

Concatenated (Combined) Text and Fields

[Last]& ", "&[First]

Calculated Field (Using Two Fields)

[SalePrice]-[Cost]

Calculated Field (Using a Field and a Constant)

[SalePrice]*0.1

TO CREATE AN EXPRESSION WITH THE EXPRESSION BUILDER:

  1. DISPLAY THE QUERY IN DESIGN VIEW.

  2. CLICK THE FIELD ROW OF A BLANK COLUMN IN THE DESIGN GRID.

  3. CLICK THE BUILD BUTTON ON THE TOOLBAR, DOUBLE-CLICK THE FIELD YOU WANT TO USE IN THECALCULATION, CLICK THE BUTTON THAT CORRESPONDS TO THE CALCULATION YOU WANT, AND THEN CLICK OR TYPE ANY OTHER FIELDS OR VALUES YOU WANT TO USE.

  4. CLICK OK.