Learn to build an expression

Access for Microsoft 365 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007

You can use expressions to do all kinds of things with your data. For example, determine how many days have passed since an order was shipped, or combine a FirstName and a LastName into a FullName. The following sections show you how to create an expression step-by-step.

In this article

Calculate values for controls in forms and reportsCreate a calculated field in a querySet default values for a table fieldAdd a validation rule to a table field or recordGroup and sort data in reportsUse expressions as query criteriaCreate a calculated field in a tableSet default values for controlsAdd a validation rule to a controlControl which macro actions are run

Calculate values for controls in forms and reports

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 come from a table or a query. The Sum function calculates the total for all the values of table_field.

PROCEDURE

  1. In the Navigation Pane, right-click the form that you want to change, and then press Layout View or Design View on the shortcut menu.
  2. Pick the control into which you want to enter an expression.
  3. If the Property Sheet is not already displayed, press F4 to present it.
  4. To manually create an expression, on the Data tab in the property sheet, choose the ControlSource property of the text box, and then enter = 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 design an expression by using the Expression Builder, select the Build button Button image in the property box. When you have completed your expression, the property sheet will look like this:
An expression in the Control Source property of a text box

Top of Page

Use expressions as query criteria

You apply criteria in a query to restrict query results. You specify the criteria as an expression, and Access capturess only those rows that match the expression.

For example, suppose that you want to see all the orders whose shipped date occurred in the first three months of the year 2017. 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/2017# And #3/31/2017#

The ShippedDate column will imitate 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. Remember that in the expression, you surround the dates with pound signs (#). Access presumes a value enclosed in pound signs to be a Date/Time data type. Treating those values as date/time data allows you to run calculations on those values, such as subtracting one date from another.

PROCEDURE

  1. In the Navigation Pane, right-click the query that you want to change, and then choose Design View on the shortcut menu.
  2. Press in the Criteria cell in the column for which you want to enter your criteria.
  3. To manually produce your expression, state your criteria expression. Do not precede the criteria expression with the = operator.
  4. To formulate your expression by using the Expression Builder, on the ribbon, pick Design, and then in the Query Setup group, select Builder Button image .If you want a larger area in which to edit the expression, position the cursor in the Criteria cell and then press SHIFT+F2 to present the Zoom box:
An expression in the Zoom dialog box.

Tip:   To make the text more readable, select Font.

Top of Page

Create a calculated field in a query

Consider you’re designing a query, and you want to highlight the results of a calculation using other fields in the query. To design the calculated field, you state 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

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

A calculated field, shown in Datasheet view

PROCEDURE

  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.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.
  4. To create your expression by using the Expression Builder, on the ribbon, click Design, and then in the Query Setup group, click Builder.

Top of Page

Create a calculated field in a table

In Access, you can create a calculated field in a table. This eliminates the need for a separate query to do calculations. For example, if you have a table that lists the quantity, price and tax rate for each item in an order, you could add a calculated field that shows the total price, like so:[Quantity]*([UnitPrice]+([UnitPrice]*[TaxRate]))

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

PROCEDURE

  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.
  4. Enter the calculation that you want for this field, for example:[Quantity] * [Unit Price]For a calculated field, you don’t start the expression with an equal sign (=).
  5. Click OK.Access adds the calculated field, and then highlights the field header so that you can type a field name.
  6. Type a name for the calculated field, and then press ENTER.

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 – a value that Access uses for new records unless another value is provided. For example, suppose that you want to automatically insert the date and time into a field called OrderDate whenever someone adds a new record. To do this, you might use the following expression:Now()

PROCEDURE

  1. In the Navigation Pane, double-click the table that you want to change.Access opens the table in Datasheet view.
  2. Select the field that you want to change.
  3. On the ribbon, click Fields, and in the Properties group, click Default Value. Access displays the Expression Builder.
  4. 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

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 is like 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.

PROCEDURE

  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

Add a validation rule to a table field or record

Expressions are very useful for validating data as it is entered into the database – keeping bad data out. In tables, there are two kinds of validation rules: Field validation rules (which prevent users from entering bad data in a single field), and record validation rules (which prevent users from creating records that don’t meet the validation rule). You use expressions for both kinds of validation rules.

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

PROCEDURE: Enter a field or record validation rule

  1. In the Navigation Pane, double-click the table that you want to change. Access opens the table in Datasheet view.
  2. For a field validation rule, select the field that you want to change.
  3. On the ribbon, click Fields, then in the Field Validation group click Validation, and then click Field Validation Rule or Record Validation Rule. Access displays the Expression Builder.
  4. 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: >=0Do not precede the expression with an equal sign (=).

Validation rule expressions are Boolean, which means that they are either True or False for any given input value. A validation rule must be True for the value or Access doesn’t save the input and displays a validation message indicating the error. In this example, if you enter a value for the Units On Hand field that is less than zero, the validation rule is 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.

PROCEDURE: 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.”

  1. In the Navigation Pane, double-click the table that you want to change.Access opens the table in Datasheet view.
  2. For a field validation message, select the field to which you added the validation rule.
  3. On the ribbon, click Fields, then in the Field Validation group click Validation, and then click Field Validation Message or Record Validation Message.
  4. 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

Add a validation rule to a control

In addition to table fields and records, controls even have a Validation Rule property that can take 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/2017. You can format the Validation Rule and ValidationText properties for the text box where you state the beginning date to the following:

PropertySetting
Validation Rule>=#1/1/2017#
Validation TextYou cannot enter a date earlier than 1/1/2017.

If you try to type a date earlier than 1/1/2017, a message emerges, and displays the text in the ValidationText property. If no text was entered in the ValidationText property box, Access shows a generic message. After you press OK, Access returns you to the text box.

Setting a validation rule for a table field imposes the rule across the database, wherever that field is modified. Alternatively, defining a validation rule for a control on a form mandates the rule only anytime that form is being used. Setting validation rules independently for table fields and for controls on forms can be useful if you want to construct various validation rules for different users.

PROCEDURE

  1. In the Navigation Pane, right-click your desired form or report to modify, and then select Design View or Layout View on the shortcut menu.
  2. Right-click the control that you want to change, and then press Properties on the shortcut menu. Access presents the property sheet for the control.
  3. Select the All tab, and then click the Validation Rule property box.
  4. State the expression, or choose the Build button Button image in the property box to create an expression by using the Expression Builder. Do not place the = operator before the expression with.
  5. To customise the text that appears if a user enters data that does not match the validation rule, input the text that you want in the Validation Text property.

Top of Page

Group and sort data in reports

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

Grouping is the process of merging columns with 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 offers 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 implementing 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 additional characters in a particular order, such as alphabetical order.

PROCEDURE: Add grouping and sorting to a report

  1. In the Navigation Pane, right-click your preferred report to edit, and then select Layout View or Design View on the shortcut menu.
  2. On the ribbon, pick Design, and in the Grouping & Totals group, select Group & Sort. The Group, Sort, and Total pane comes up below the report.
  3. To insert a grouping level to the report, click Add a group.
  4. To create a sort order to the report, click Add a sort. A new group level or sort order emerges in the pane, plus a list of the fields that provide data for the report. This figure depicts a usual new group level (grouping on Category) and sort order (sorting on Manufacturer), plus a list that encompasses 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, press expression to start using the Expression Builder.
  2. Type the expression you want to use in the expression box (the upper box) of the Expression Builder. Ensure that you start the expression with the equal (=) operator.

PROCEDURE: Add an expression to an existing group or sort

  1. In the Navigation Pane, right-click your target report tha to revise, and then choose Layout View or Design View on the shortcut menu.
  2. Choose your desired the group level or sort order to update.
  3. Select the down arrow beside Group on (for grouping levels) or Sort by (for sort orders). A list that has the available fields displays.
  4. At the bottom of the list that includes the fields, choose expression to start using the Expression Builder.
  5. Enter your expression in the expression box (the upper box) of the Expression Builder. Check that you begin your expression with the equal (=) operator.

Top of Page

Control which macro actions are run

In some cases, you may want to conduct an action or series of actions in a macro only in the event that a specific 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 enforce this rule, you use an expression in an If block in the macro to specify the condition in the macro.

In this example, assume the text box is named “Items.” The expression that sets the condition is:[Items]>=10

An expression used in an If block in a macro

PROCEDURE

  1. In the Navigation Pane, right-click your sought macro to alter, and then choose Design View on the shortcut menu.
  2. Select the If block that you want to modify, or insert an If block from the Action Catalogue pane.
  3. Pick the top line of the If block.
  4. Enter your conditional expression in the box, or press the Build button Button image beside the expression box to begin using the Expression Builder.

The expression you write must be Boolean, meaning it is either True or False. The macro actions within the If block process only anytime the condition is True.

Top of Page

See Also

Use the Expression Builder

Introduction to expressions

Guide to expression syntax

Examples of expressions

This information was compiled using information courtesy of © Microsoft 2020. All rights reserved.

Leave a Reply

%d bloggers like this: