Create an expression

This article describes how to create and use expressions in Microsoft Office Access. You use expressions to perform many of the same tasks for which you use formulas in Microsoft Office Excel. This article shows you where to use expressions, depending on the specific task that you want to perform.

In this article


Overview of expressions

An expression is a combination of some or all of the following: built-in or user-defined functions, identifiers, operators, and constants. Each expression evaluates to a single value.

The components of an expression.

For example, the following expression contains all four of these elements:

=Sum([Purchase Price])*0.08

In this example, Sum() is a built-in function, [Purchase Price] is an identifier, * is a mathematical operator, and 0.08 is a constant. This expression can be used in a text box in a form footer or report footer to calculate sales tax for a group of items.

Expressions can be much more complex or much simpler than this example. For example, this Boolean expression (an expression that evaluates to either True or False) consists of only an operator and a constant:

>0

This expression returns True when it is compared to a number that is greater than 0, and returns False when it is compared to a number that is less than 0. You can use this expression in the Validation Rule property of a control or table field to ensure that only positive values are entered.

In Access, expressions are used in many places to perform calculations, manipulate characters, or test data. Tables, queries, forms, reports, and macros all have properties that accept an expression. For example, you can use expressions in the Control Source and Default Value properties for a control. You can also use expressions in the Validation Rule property for a table field. In addition, when you write Microsoft Visual Basic for Applications (VBA) code for an event procedure or for a module, you often use expressions that resemble those that you use in an Access object, such as a table or query.

The following sections describe the most common ways in which you can use expressions.

Top of Page Top of Page

New features in the Access 2010 Expression Builder

In Access 2010, the Expression Builder has been redesigned to be easier to use. Here are some changes that you will notice in this version:

  • IntelliSense    IntelliSense functionality has been added to almost every context where you can enter an expression. This means that as you begin to type an identifier or function, Access displays a list of available identifiers and functions that are appropriate for the context you are in. You can continue typing to refine the list, or choose an item to have it filled in for you. This greatly speeds the process of typing expressions, and helps ensure the accuracy of the expressions you create.

IntelliSense is especially helpful as you build Web databases by using the new Web publishing feature. Only certain functions are compatible with Web databases, and IntelliSense prevents you from using functions that are incompatible.

For more information about Web databases, see the article Build a database to share on the Web.

  • Quick Tips    While IntelliSense is displaying a list of identifiers and functions, you can click each item in the list to view a short description of the item.
  • Quick Info    As soon as you type a complete function name into the Expression Builder, Access pops up a small box that displays the syntax of the expression. This helps you remember which arguments the function requires (if any), and in what order. Access bolds each argument in the Quick Info display as you are typing it into the expression box, so that you always know where you are in the argument list.

 Note   These features are also available in most contexts where you can type expressions, even if you are not using the Expression Builder dialog box.

In addition to these new features, the Expression Builder dialog box itself has been redesigned:

  • You can click << Less or More >> to control how much of the Expression Builder dialog box is displayed.
  • When you resize the Expression Builder dialog box, the new size is retained for the duration of your Access session, keeping you from having to resize the dialog box each time you open it.
  • The row of operator buttons has been removed, resulting in a less cluttered dialog box.

Top of Page Top of Page

About nested expressions in Web databases

Expressions are often "nested" within other expressions. For example, consider the following:

=len("a" & "b") + len("c" & "d") + len("e" & "f")

This expression is said to be nested one level deep, because each concatenation operation is nested within a len() function. In a client database, there is no specific limit to the number of levels to which you can nest expressions. However, in a Web database, the maximum number of nesting levels is 65. Expressions that are nested more than 65 levels deep will not work in the browser, and the Access Compatibility Checker does not display any messages indicating that this will occur.

 Important    The use of the &, AND, and OR operators can create additional nesting levels on the server that aren't obvious in the Access client. For example the expression "a" & "b" & "c" does not appear to be nested in the client, but on the server, it is translated to concatenate.Db("a", concatenate.Db("b", "c") ). This translation creates one level of nesting. Using many consecutive &, AND, or OR operators in a single expression can cause you to exceed the server's nesting limit of 65, at which point the expression will not work in the browser.

For more information about Web databases, see the article Build a database to share on the Web.

Top of Page Top of Page

Use expressions in form and report controls

When you use an expression as the data source for a control, you create a calculated control. For example, suppose that you have a report that displays multiple inventory records, and you want to create a total in the report footer that sums all the line items on the report.

A total on a report.

To calculate the total, place a text box control in the report footer, and then set the ControlSource property of the text box to the following expression:

=Sum([table_field])

In this case, table_field is the name of the field that contains your subtotal values. That field can reside in a table or a query. The Sum function calculates the total for a set of values from your record source.

The following procedure shows how to enter an expression in a text box control.

Enter an expression in a text box control

  1. In the Navigation Pane, right-click the form that you want to change, and then click Layout View or Design View on the shortcut menu.
  2. Select the control into which you want to enter an expression.
  3. If the Property Sheet is not already displayed, press F4 to display it.
  4. To manually create an expression, on the Data tab in the property sheet, click the ControlSource property of the text box, and then type = followed by the rest of your expression. For example, to calculate the subtotal shown above, type =Sum([table_field]), making sure that you substitute the name of your field for table_field.
  5. To create an expression by using the Expression Builder, click the Build button Button image in the property box.

After completing your expression, the property sheet will resemble the following:

An expression in the Control Source property of a text box.

Top of Page Top of Page

Use expressions as query criteria

You can use an expression to define criteria in a query. Access then returns only those rows that match the criteria. For example, suppose that you want to see all the orders whose shipped date occurred in the first three months of the year 2010. To enter the criteria, you type the following expression in the Criteria cell for the Date/Time column in your query. This example uses a Date/Time column called ShippedDate. To define a date range, enter your criteria in this manner:

Between #1/1/2010# And #3/31/2010#

The ShippedDate column will resemble the following.

An expression in the Criteria row of the query grid.

For each record in the Orders table, if the value in the ShippedDate column falls in the date range that you specify, the record is included in the query output. Note that in the expression, you enclose the dates with pound signs (#). Access treats a value enclosed in pound signs as a Date/Time data type. Treating those values as date/time data enables you to perform calculations on those values, such as subtracting one date from another.

Enter criteria in the query design grid

  1. In the Navigation Pane, right-click the query that you want to change, and then click Design View on the shortcut menu.
  2. Click in the Criteria cell in the column for which you want to enter your criteria.
  3. To manually create your expression, type your criteria expression.

 Note   Do not precede the criteria expression with the = operator.

  1. To create your expression by using the Expression Builder, on the Design tab, in the Query Setup group, click Builder Button image.

 Tip    If you want a larger area in which to edit the expression, place the cursor in the Criteria cell and then press SHIFT+F2 to display the Zoom box:

An expression in the Zoom dialog box.

Top of Page Top of Page

Create a calculated field in a query

Suppose that you are designing a query, and you want to display the results of a calculation that involves other fields in the query. To create the calculated field, you enter an expression in a blank cell in the Field row in your query. For example, if you have a query that contains a Quantity field and a Unit Price field, you can multiply the two to create a calculated field for Extended Price by entering the following expression in the Field row of the query:

Extended Price: [Quantity] * [Unit Price]

Prefacing the expression with the text Extended Price: names the new column Extended Price. This name is often called an alias. If you do not supply an alias, Access will create one, such as Expr1.

Using an expression to create a calculated field in a query.

When you run the query, Access performs the calculation on each row, as shown in the following illustration:

A calculated field, shown in Datasheet view.

Create a calculated field in query Design view

  1. In the Navigation Pane, right-click the query that you want to change, and then click Design View on the shortcut menu.
  2. Click the Field cell in the column where you want to create the calculated field.
  3. To manually create your expression, type your expression.

 Note   Do not precede the criteria expression with the = operator; instead, begin the expression with a descriptive label followed by a colon. For example, type Extended Price: to provide the label for an expression that creates a calculated field called Extended Price. Then, enter the criteria for your expression after the colon.

  1. To create your expression by using the Expression Builder, on the Design tab, in the Query Setup group, click Builder.

Top of Page Top of Page

Create a calculated field in a table

In Access 2010, you can create a calculated field in a table. This eliminates the need for a separate query to do calculations.


 Notes 

  • The calculation cannot include fields from other tables or queries.
  • The results of the calculation are read-only.

To create the calculated field:

  1. Open the table by double-clicking it in the Navigation Pane.
  2. Scroll horizontally to the rightmost column in the table, and click the Click to Add column heading.
  3. In the list that appears, click Calculated Field, and then click the data type that you want for the result.

Access displays the Expression Builder.

  1. Begin typing the calculation that you want for this field, for example:

[Quantity] * [Unit Price]

 Note   It is not necessary to precede the expression with an equal sign (=).

  1. Click OK.

Access adds the calculated field, and then highlights the field header so that you can type a field name.

  1. Type a name for the calculated field, and then press ENTER.

Top of Page Top of Page

Add a validation rule to a table field or record

Expressions are very useful for validating data as it is entered into the database. In tables, you can create two kinds of validation rules: Field validation rules, which prevent users from entering invalid data into a single field, and record validation rules, which prevent users from creating records that don't meet the criteria that you enter. Both kinds of validation rules are created by using expressions.

For example, suppose that you have a table named Inventory with a field that is named Units On Hand, and you want to set a rule that forces users to enter a value greater than or equal to zero. In other words, inventory can never be a negative number. You can do this by using the following expression as a field validation rule in the Units On Hand field:

>=0

Enter a validation rule

Use the following procedure to enter either a field validation rule or a record validation rule:

  1. In the Navigation Pane, double-click the table that you want to change.

Access opens the table in Datasheet view.

  1. For a field validation rule, select the field that you want to change.
  2. On the Fields tab, in the Field Validation group, click Validation, and then click Field Validation Rule or Record Validation Rule.

Access displays the Expression Builder.

  1. Begin typing the criteria that you want. For example, for a field validation rule that requires that all values are greater than or equal to zero, type the following:

>=0

 Note   Do not precede the expression with an equal sign (=).

Validation rule expressions are Boolean, meaning that they resolve to either True or False. A validation rule must resolve to True for the value to be accepted. In this example, if you enter a value for the Units On Hand field that is less than zero, the expression evaluates to False, and Access does not accept the value. If you haven't entered a validation message as described in the following section, Access displays its own message to indicate that the value you entered is prohibited by the validation rule for the field.

Enter a validation message

To make your database easier to use, you can enter custom validation messages. These replace the generic messages that Access displays when data does not match a validation rule. You can use custom validation message to provide specific information that helps the user enter the correct data, for example, "The Units On Hand value cannot be a negative number." Use the following procedure to create a validation message:

  1. In the Navigation Pane, double-click the table that you want to change.

Access opens the table in Datasheet view.

  1. For a field validation message, select the field to which you added the validation rule.
  2. On the Fields tab, in the Field Validation group, click Validation, and then click Field Validation Message or Record Validation Message.
  3. In the Enter Validation Message dialog box, type the message that you want to appear when data does not match the validation rule, and then click OK.

Top of Page Top of Page

Add a validation rule to a control

 Note   Validation rules are not available for controls in Web databases. Use field or record validation rules instead, as described in the preceding section.

In addition to table fields and records, controls also have a Validation Rule property that can accept an expression. For example, suppose that you use a form to enter the date range for a report, and you want to ensure that the beginning date isn't earlier than 1/1/2010. You can set the Validation Rule and ValidationText properties for the text box where you enter the beginning date to the following:

Property Setting
Validation Rule >=#1/1/2010#
Validation Text You cannot enter a date earlier than 1/1/2010.

If you try to enter a date earlier than 1/1/2010, a message appears, and displays the text in the ValidationText property. If no text was entered in the ValidationText property box, Access displays a generic message. After you click OK, Access returns you to the text box.

 Tip   Setting a validation rule for a table field enforces the rule throughout the database, wherever that field is modified. Alternatively, setting a validation rule for a control on a form enforces the rule only when that form is being used. Setting validation rules separately for table fields and for controls on forms can be useful if you want to establish different validation rules for different users.

Enter a validation rule for a control

  1. In the Navigation Pane, right-click the form or report that you want to change, and then click Design View or Layout View on the shortcut menu.
  2. Right-click the control that you want to change, and then click Properties on the shortcut menu.

Access displays the property sheet for the control.

  1. Click the All tab, and then click the Validation Rule property box.
  2. Type the expression, or click the Build button Button image in the property box to create an expression by using the Expression Builder.

 Note   Do not precede the expression with the = operator.

  1. To customize the text that appears if a user enters data that does not match the validation rule, type the text that you want in the Validation Text property.

Top of Page Top of Page

Set default values for a table field

You can use an expression to specify a default value for a field in a table. For example, suppose that you want to automatically insert the date and time into a field called OrderDate when you add a new record. To do this, you might use the following expression:

Now()

Specify a default value for a field in a table

  1. In the Navigation Pane, double-click the table that you want to change.

Access opens the table in Datasheet view.

  1. Select the field that you want to change.
  2. On the Fields tab, in the Properties group, click Default Value.

Access displays the Expression Builder dialog box.

  1. Type your expression in the box, making sure to begin the expression with an equal sign (=).

 Note   If you bind a control to a table field and both the control and table field have default values, the control's default value takes precedence over the table field.

Top of Page Top of Page

Set default values for controls

Another common place to use an expression is in the Default Value property of a control. The Default Value property of a control behaves similarly to the Default Value property of a field in a table. For example, to use the current date as the default value for a text box, you can use the following expression:

Date()

This expression uses the Date function to return the current date, but not the time. If you bind the text box to a table field, and the field has a default value, the control's default value takes precedence over the table field. It often makes better sense to set the Default Value property for the field in the table. That way, if you base several controls for different forms on the same table field, the same default value will apply to each control, helping ensure consistent data entry on each form.

Enter a default value for a control

  1. In the Navigation Pane, right-click the form or report that you want to change, and then click Design View or Layout View on the shortcut menu.
  2. Select the control that you want to change.
  3. If the property sheet is not already displayed, press F4 to display it.
  4. Click the All tab on the property sheet, and then click the Default Value property box.
  5. Type the expression, or click the Build button Button image in the property box to create an expression by using the Expression Builder.

Top of Page Top of Page

Control which macro actions are run

In some cases, you might want to carry out an action or series of actions in a macro only if a particular condition is true. For example, suppose that you want a macro action to run only when the value in a text box is greater than or equal to 10. To set this rule, you use an expression in an If block in the macro to define the condition in the macro.

In this example, assume the text box is named "Items." Your expression that sets the condition is [Items]>=10.

An expression used in an If block in a macro.

Enter an expression in an If block in a macro

  1. In the Navigation Pane, right-click the macro that you want to change, and then click Design View on the shortcut menu.
  2. Click the If block that you want to modify, or add an If block from the Action Catalog pane.
  3. Click the top line of the If block.
  4. Type your conditional expression in the box, or click the Build button Button image next to the expression box to launch the Expression Builder.

 Note   The expression you type must be Boolean, meaning it resolves to either True or False. The macro actions within the If block run only when the condition resolves to True.

Top of Page Top of Page

Group and sort data in reports

You use the Group, Sort, and Total pane to define grouping levels and sorting orders for the data in a report. Most typically, you group or sort on a field that you select from a list. However, if you want to group or sort on a calculated value, you can enter an expression instead.

Grouping is the process of combining columns that contain duplicate values. For example, suppose that your database contains sales information for offices in different cities, and that one of the reports in your database is named "Sales by City." The query that provides the data for that report groups the data by your city values. This type of grouping can make your information easier to read and understand.

In contrast, sorting is the process of imposing a sort order on the rows (the records) in your query results. For example, you can sort records by their primary key values (or another set of values in another field) in either ascending or descending order, or you can sort the records by one or more characters in a specified order, such as alphabetical order.

Add grouping and sorting to a report

  1. In the Navigation Pane, right-click the report that you want to change, and then click Layout View or Design View on the shortcut menu.
  2. On the Design tab, in the Grouping & Totals group, click Group & Sort.

The Group, Sort, and Total pane appears below the report.

  1. To add a grouping level to the report, click Add a group.
  2. To add a sort order to the report, click Add a sort.

A new group level or sort order appears in the pane, plus a list of the fields that provide data for the report. This figure shows a typical new group level (grouping on Category) and sort order (sorting on Manufacturer), plus a list that contains the available fields for grouping and sorting:

Choosing the expression option in the Group, Sort, and Total pane.

  1. Under the list of available fields, click expression to start the Expression Builder.
  2. Enter the expression you want to use in the expression box (the upper box) of the Expression Builder. Make sure that you begin the expression with the equal (=) operator.

Add an expression to an existing group or sort

  1. In the Navigation Pane, right-click the report that you want to change, and then click Layout View or Design View on the shortcut menu.
  2. Click the group level or sort order that you want to change.
  3. Click the down arrow next to Group on (for grouping levels) or Sort by (for sort orders).

A list that contains the available fields appears.

  1. At the bottom of the list that contains the fields, click expression to start the Expression Builder.
  2. Type your expression in the expression box (the upper box) of the Expression Builder. Make sure that you begin your expression with the equal (=) operator.

Top of Page Top of PageTop of Page Top of Page

Try Office 2010 today!

 
 
Applies to:
Access 2010, SharePoint Server 2010