Create an expression

This article explains how to create and use expressions in Microsoft Access. You use expressions to perform many of the same tasks for which you use formulas in Microsoft Excel. This article shows you where to use expressions, depending on the specific task that you want to accomplish, and it shows you how to manually create expressions and how to create expressions by using the Expression Builder tool.

In this article


Overview of expressions

An expression is a combination of some or all of the following: built-in or user-defined functions (Function procedure: A procedure that returns a value and that can be used in an expression. You declare a function with the Function statement and end it with the End Function statement.), identifiers (identifier (expressions): An element of an expression that refers to the value of a field, control, or property. For example, Forms![Orders]![OrderID] is an identifier that refers to the value in the OrderID control on the Orders form.), operators (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.), and constants (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.). 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 might 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 (Boolean: An expression that can be evaluated as either true (nonzero) or false (0). You can use the keywords True and False to supply the values of -1 and 0, respectively. The field data type Yes/No is Boolean and has the value of -1 for Yes) expression consists of just 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 or equal to 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.

The following table shows more examples of expressions:

Expression Purpose
=[RequiredDate]-[ShippedDate] By subtracting one identifier from another, you can calculate the difference between the values in two text box controls on a report.
Date() By using the Date function, you can set the default value for a field in a table to the current date.
ExtendedPrice: CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100 By using functions, identifiers, and constants, you can create a calculated field named “ExtendedPrice” in a query. The CCur function converts the result of the calculation to the Currency data type.
Between #1/1/2005# And #12/31/2005# By using date constants (enclosed in pound signs to signify that Access should treat them as dates), you can enter criteria for a Date/Time field in a query.
=[Orders Subform].Form!OrderSubtotal By using the complete identifier for the OrderSubtotal control on the Orders subform, you can display its value on the main form.
=Format(Date(),"mmmm d, yyyy") By using the Format function, you can format a date in a variety of ways. “mmmm” specifies that the month name is spelled out in full; “d” specifies that the day is displayed with either 1 or 2 digits, and “yyyy” specifies that the year is displayed with 4 digits. For example, on 2/8/2006 this expression would return “February 8, 2006”.

As shown in the preceding table, 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. Some examples include:

  • The Control Source and Default Value properties for a control
  • The Validation Rule property for a table field
  • The Field or Criteria row of a query
  • A macro or Microsoft Visual Basic for Applications (VBA) module

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

Top of Page Top of Page

Create a calculated control on a form or report

When you use an expression as the data source for a control, the control is referred to as a calculated control. For example, suppose that you have an order form that displays multiple order records, and you want to create a subtotal in the form footer that sums all of the line items on the form.

Subtotal on an order form

To calculate the subtotal, place a text box control in the form 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 Design View.
  2. Right-click the text box that you want to change, and then click Properties.
  3. 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.
  4. To create an expression by using the Expression Builder, click the Build button Button image in the property box.

Your property sheet will look similar to the following:

Sample expression in the Control Source property

Top of Page Top of Page

Create a calculated field in a query

You can use an expression to create a calculated field in a query. For example, suppose that you are designing a query, and you want to display the year in which an order was shipped. To create the calculated field, you enter the following expression in a blank cell in the Field row in your query:

Shipped Date: Format([ShippedDate],"yyyy")

In this case, the expression uses the Format function to extract the year from the values — the year is contained in a field called ShippedDate. The Format function retrieves the shipped date from that field, uses the "yyyy" argument to format the date to show only the four digits of the year, and places the resulting value in a new column. By prefacing the expression with the text Shipped Date:, you can assign a meaningful name to the new column. This name is often called an alias (alias (SQL): An alternative name for a table or field in expressions. Often used to shorten the table or field name for subsequent references in code, to prevent possible ambiguous references, or to provide a more descriptive name in query output.). If you do not supply an alias, Access will create one, such as Expr1.

An expression that extracts the year from a date value

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

 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.

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 year 2004. 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/2004# And #12/31/2004#

The ShippedDate column will look similar to the following.

Using Between operator in query design 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 within 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.
  4. To create your expression by using the Expression Builder, on the Design tab, in the Query Setup group, click Builder.

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

 Tip    If you want a larger area in which to enter an expression, press SHIFT+F2 to display the Zoom box.

Zoom box

Top of Page Top of Page

Validate data in a table field

Expressions are also useful for validating data as it is entered into the database. You validate data by entering an expression in the Validation Rule property of a field in a table. For example, suppose that you have a table called Inventory with a field called 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 rule in the Validation Rule property:

>=0

Enter a validation rule for a field in a table

  1. In the Navigation Pane, right click the table that you want to change, and then click Design View on the shortcut menu.
  2. In the Field Name column, click the field that you want to change.
  3. On the General tab in the property sheet, click the Validation Rule property box.
  4. To manually create your expression, type your expression.
  5. To create your expression by using the Expression Builder, click the Build button Button image in the property box.

     Note    Do not precede the expression with the = operator when you create a validation rule.

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. Then, Access displays the text shown in the Validation Text property box, and does not accept the value. If you haven't entered any text in the Validation Text property box, Access displays its own message to indicate that the value you entered is prohibited by the validation rule for the field.

Find links to more information about creating data validation rules in the See Also section.

Top of Page Top of Page

Validate data in a control on a form or report

In addition to table fields, 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/2004. 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/2004#
Validation Text You cannot enter a date earlier than 1/1/2004.

If you try to enter a date earlier than 1/1/2004, a message appears. After you click OK, Access returns you to the text box.

Message box with validation text for Sales by Year dialog box

 Tip   Setting a validation rule for a table field enforces the rule for all users of the table; alternatively, setting a validation rule for a control on a form enforces the rule only for users of that form. 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 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 when you create a validation rule.

  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.

If the Control Source property for your control is a field in a table, it is a good idea to set the field's Validation Rule property in addition to setting the Validation Rule property for the control. That way, the same rule is enforced all the time, no matter which form or query is used to update the field.

Find links to more information about creating data validation rules in the See Also section.

Top of Page Top of Page

Set a default value for a table field

You can use an expression to store 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 can enter the following expression in the Default Value property:

Now()

Enter a default value for a field in a table

  1. In the Navigation Pane, right-click the table that you want to change, and then click Design View on the shortcut menu.
  2. In the Field Name column, click the field that you want to change.
  3. On the General tab in the property sheet, click the Default Value property box.
  4. Type your expression, or click the Build button Button image in the property box to create an expression by using the Expression Builder.

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.

Find links to more information about setting default values in the See Also section.

Top of Page Top of Page

Set a default value for a control on a form or report

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 a number of 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 on the shortcut menu.
  2. Right-click the control that you want to change, and then click Properties on the shortcut menu.
  3. Click the All tab on the property sheet, and then click the Default Value property box.
  4. Type the expression, or click the Build button Button image in the property box to create an expression by using the Expression Builder.

Find links to more information about setting default values in the See Also section.

Top of Page Top of Page

Use expressions to carry out macro actions

In some cases, you may 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 equals 10. To set this rule, you use an expression to define the condition in the Condition column of the macro.

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

A conditional expression in a macro

Enter a condition for a macro action

  1. In the Navigation Pane, right-click the macro that you want to change, and then click Design View on the shortcut menu.
  2. If you do not see the Condition column in the macro design grid, on the Design tab (on the Ribbon), in the Show/Hide group, click Conditions.
  3. In the Condition column, click the blank cell next to the macro action that you want to change, and then type your conditional expression.

As with the Validation Rule property, the expression in the Condition column is Boolean, meaning it resolves to either True or False. The macro action runs only when the condition resolves to True.

Top of Page Top of Page

Group and sort data in a report by using an expression

You use the Group, Sort, and Total pane to define grouping levels and sorting orders for the data in a report. The pane replaces the Sorting and Grouping dialog box that appeared in earlier versions of Access. You can display and use the Group, Sort, and Total pane only when you open a report in Design view — the pane appears in the same tab as your report.

The following figure illustrates the pane as it appears when it first opens:

The Group, Sort, and Total pane

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 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 at the bottom of the tab that contains your 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 illustrates 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:

The Group, Sort, and Total pane , with the field list showing

  1. In the list that contains the available fields, click expression to start the Expression Builder.
  2. Enter your expression 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 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.

For more information about using the Expression Builder, see the next section.

Top of Page Top of Page

Use the Expression Builder to create expressions

You can use the Expression Builder to help build expressions. The Expression Builder provides easy access to the names of the fields and controls in your database, and to many of the built-in functions that are available to you when you write expressions.

 Tip    Try Office 2010 New features such as AutoText and Quick Info have been added to the Expression Builder in Access 2010, making it easier than ever to create expressions.
Read an article or try Office 2010!

Understanding the Expression Builder

You can think of the Expression Builder as a way to look up and insert components of an expression that you might have trouble remembering, such as identifier names (for example, fields, tables, forms, and queries), and function names and their arguments.

You can use the Expression Builder to create a new expression, or you can select from prebuilt expressions, including expressions for displaying page numbers, the current date, and the current date and time.

You can start the Expression Builder from most of the places in Access where you would write expressions manually, such as the Control Source property of a control or the Validation Rule property of a table field. As a rule, if you see the Build button Button image, you can click it to start the Expression Builder.

Expression Builder

Callout 1 Expression box

The upper section of the builder contains an expression box where you construct your expression. You can type your expression in the box manually, or you can select elements from the three columns in the lower section of the Expression Builder and then add them to your expression box. To add an element, double-click it, and then click Paste.

Callout 2 Operator buttons

The middle section of the Expression Builder displays buttons for inserting common arithmetical and logical operators into your expression. To insert an operator in the expression box, click the appropriate button. To display a longer list of operators that you can use in expressions, click the Operators folder in the lower-left column that contains expression elements, and then click the category that you want in the middle column. The right column then lists all the operators in the selected category. To insert an operator, double-click it.

Callout 3 Expression elements

The lower section contains three columns:

  • The left column displays folders that list the tables, queries, forms, and reports in your database, as well as the available built-in functions and user-defined functions, constants, operators, and common expressions.
  • The middle column lists specific elements or categories of elements for the folder that is selected in the left column. For example, if you click Built-In Functions in the left column, the middle column lists function categories.
  • The right column lists the values, if any, for the elements that you selected in the left and middle columns. For example, if you click Built-In Functions in the left column and then click Date/Time in the middle column, the right column lists all the built-in functions that you can use to work with date and time values.

To construct your expression, you can type text in the expression box and paste elements from the other areas in the Expression Builder. For example, you can click in the lower-left column to see any of the objects in your database, in addition to the available functions, constants, operators, and common expressions. When you click an item in the left column, the contents of the other columns change accordingly. For example, when you double-click Tables in the left column and then click the name of a table, the middle column lists the fields in that table. When you double-click Functions in the left column and then click Built-In Functions, the middle column lists all the function categories, and the right column lists all the functions for those categories. When you double-click a function to insert it into your expression, the function and the text that indicates the needed arguments for that function appear as placeholder text in the expression box. For example, if you double-click the IIf function, the Expression Builder adds the following to the expression box:

IIf (<<expr>>, <<truepart>>, <<falsepart>>)

You must replace the text <<expr>>, <<truepart>>, and <<falsepart>>with the actual argument values. You can do this directly in the expression box by clicking each placeholder, and then either typing the argument or selecting the argument by using the three expression element lists.

If you double-click two or more functions in succession, for example, the IIf function and then the Round function, the Expression Builder adds both functions to the expression box, separated by the placeholder <<Expr>>:

IIf (<<expr>>, <<truepart>>, <<falsepart>>) <<Expr>> Round (<<number>>, <<precision>>)

In addition to replacing the placeholders for the function arguments, You must replace the placeholder <<Expr>> with an operator before the overall expression will be valid.

When you paste an identifier (the name of a table field or control) into your expression, the Expression Builder inserts 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 a form called Customers, and you then paste an identifier for the Visible property of the form into 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.

Start the Expression Builder from a table

In the Navigation Pane, right-click the table that you want to change, and then click Design View on the shortcut menu.

In the Field Name column, click the field that you want to change.

Under Field Properties, click the General tab.

Click the property that will contain the expression, and then click the Build button Button image next to the property.

Start the Expression Builder from a form or report

  1. In the Navigation Pane, right-click the form or report that you want to change, and then click Design View on the shortcut menu.
  2. Right-click the control that you want to change, and then click Properties on the shortcut menu.
  3. In the property sheet, locate the property that will contain the expression, and then click the Build button Button image next to the property.

Start the Expression Builder from a query

  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 cell in the design grid that will contain the expression. For example, click the Criteria cell for the column where you want to supply criteria, or click the Field cell for the column where you want to create a calculated field.
  3. On the Design tab, in the Query Setup group, click Builder.

Top of Page Top of Page

Table of operators

An operator is a sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators. Access supports a variety of operators, including arithmetic operators such as +, -, multiply (*), and divide (/), in addition to comparison operators for comparing values, text operators for concatenating text, and logical operators for determining true or false values. This article provides details about using these operators.

Arithmetic operators

You use the arithmetic operators to calculate a value from two or more numbers or to change the sign of a number from positive to negative or vice versa.

Operator Purpose Example
+ Sum two numbers. [Subtotal]+[SalesTax]
- Find the difference between two numbers or indicate the negative value of a number. [Price]-[Discount]
* Multiply two numbers. [Quantity]*[Price]
/ Divide the first number by the second number. [Total]/[ItemCount]
\ Round both numbers to integers, divide the first number by the second number, and then truncate the result to an integer. [Registered]\[Rooms]
Mod Divide the first number by the second number, and then return only the remainder. [Registered] Mod [Rooms]
^ Raise a number to the power of an exponent. Number ^ Exponent

Comparison operators

You use the comparison operators to compare values and return a result that is True, False, or Null. The constants 1 and 2 are used in the examples, but those constants can be replaced with identifiers, functions, or expressions.

Operator Purpose Example Result
< Returns True if the first value is less than the second value. 1 < 2 True
<= Returns True if the first value is less than or equal to the second value. 1 <= 2 True
> Returns True if the first value is greater than the second value. 1 > 2 False
>= Returns True if the first value is greater than or equal to the second value. 1 >= 2 False
= Returns True if the first value is equal to the second value. 1 = 2 False
<> Returns True if the first value is not equal to the second value. 1 <> 2 True

 Note   In all cases, if either the first value or the second value is null, the result is then also null. Because null represents an unknown value, the result of any comparison with a null value is also unknown.

Logical operators

You use the logical operators to combine two Boolean values and return a true, false, or null result. Logical operators are also referred to as Boolean operators.

Operator Purpose Example Result
And Returns True when Expr1 and Expr2 are true. 1 < 2 And 3 < 4 True
Or Returns True when either Expr1 or Expr2 is true. 1 < 2 Or 3 < 4 True
Eqv Returns True when both Expr1 and Expr2 are true, or when both Expr1 and Expr2 are false. 1 < 2 Eqv 3 < 4 True
Not Returns True when Expr is not true. Not  (1 < 2) False
Xor Returns True when either Expr1 is true or Expr2 is true, but not both. 1 < 2 Xor 3 < 4 False

Concatenation operators

You use the concatenation operators to combine two text values into one.

Operator Purpose Example Result
& Combines two strings to form one string. “road” & “map” “roadmap”
“road” & Null “road”
+ Combines two strings to form one string and propagates null values (if one value is Null, the entire expression evaluates to Null). “road” + “map” “roadmap”
“road” + Null Null

Special operators

You use the special operators to return a True or False result as described in the following table.

Operator Purpose Example
Is Null or Is Not Null Determines whether a value is Null or Not Null. Field1 Is Not Null
Like "pattern" Matches string values by using the wildcard operators ? and *. Field1 Like "instruct*"
Between val1 And val2 Determines whether a numeric or date value is found within a range. Field1 Between 1 And 10
- OR -
Field1 Between #07-01-07# And #12-31-07#
In(val1,val2...) Determines whether a value is found within a set of values. Field1 In ("red","green","blue")
- OR -
Field1 In (1,5,7,9)

Find links to more information about expression syntax in the See Also section.

Top of Page Top of Page

 
 
Applies to:
Access 2007