Make your own free website on Tripod.com

Using the Expression Builder

What are Expressions?

Expressions are a fundamental part of many Microsoft Access operations and are used throughout Visual Basic code. An expression is a combination of symbols, identifiers, operators, and values, that produces a result. For example: 

LineTotal = [quantity] * [price]

The code to the right of the assignment operator (=) is an expression.

Many expressions are simple and require little effort to write, however, those involving references to forms, subforms and Access functions require you to remember complex syntax. To help you to write complex expressions as these Access provides a useful tool - Expression Builder.

 Where can I use expression builder?

How do I launch expression builder?

Right click the mouse and select builder

expBuildRightClick.jpg (26847 bytes)


How do I use expression builder?

In the example screen shot below, builder is being used to create an expression to retrieve the contents of a control, called lastNameCriteria in the form member

expBuildWindow.jpg (97642 bytes)

Notes:

  1. Column one lists database objects, functions and constants
  2. Column two lists specific elements or categories for the item selected in column one
  3. Column two lists values for items selected in the left and middle boxes

You proceed from left to right, making appropriate selections and then paste your selections. When you exit  builder (clicking the OK button) your expression will pasted into your code (as below).

codeAfterBuilder.jpg (21996 bytes)


Additional notes from the Access Help System

builderIntroHelp.jpg (96161 bytes)


In the middle section of the builder are buttons for commonly used operators. If you click one of the operator buttons, the Expression Builder inserts the operator at the insertion point in the expression box.

For a complete list of operators you can use in expressions, click the Operators folder in the lower-left box and the appropriate operator category in the middle box. The right box lists all operators in the selected category.

In the lower section of the builder are three boxes:

The left box contains folders that list the table, query, form, and report database objects, built-in and user-defined functions, constants, operators, and common expressions.


The middle box lists specific elements or categories of elements for the folder selected in the left box. For example, if you click Built-In Functions in the left box, the middle box lists categories of Microsoft Access functions.

The right box lists the values, if any, for the elements you select in the left and middle boxes. For example, if you click Built-In Functions in the left box and a category of functions in the middle box, the right box lists all built-in functions in the selected category.

In order to view new field names in the Expression Builder, you must first save the table or query containing the new fields.

If an object or function isn't listed in the lower section of the Expression Builder, it's not valid in the context where you started the builder. For example, you can't refer to other fields and controls in the validation rule for a field in table Design view, so the Tables, Queries, Forms, and Reports folders aren't available if you start the Expression Builder from the Validation Rule property of a field in table Design view.

When you paste an identifier in your expression, the Expression Builder pastes only the parts of the identifier that are required in the current context. For example, if you start the Expression Builder from the property sheet of the Customers form, and then paste an identifier for the Visible property of the form in your expression, the Expression Builder pastes only the property name: Visible. If you use this expression outside the context of the form, you must include the full identifier: Forms![Customers].Visible.