Combine text values by using an expression

To combine values from two or more text fields in Access, create an expression that uses the & operator. For example, suppose you have a form named Employees. You enter each employee's first and last name in separate fields, but you want to display the employee's full name in the form header.

To display the full name, you can use this expression:

=[FirstName] & " " & [LastName]

The expression uses the & operator to combine the values in the FirstName and LastName fields. It also uses a pair of double quotation marks (") separated by a space to insert a space between the first and last names. When you want to insert something between two fields, such as a space, punctuation, or literal text, enclose that value in quotation marks.

You can also use the following expression to display the last name and first name, separated by a comma and a space:

=[LastName] & ", " & [FirstName]

In this case, the expression inserts a comma and a space enclosed in quotation marks between the LastName and FirstName fields.

The steps in the following procedure assume that you have a form that is based on a table containing fields named FirstName and LastName. Otherwise, you can alter the expression in step 6 to suit your own data.

Add a text box with an expression for full names

1. In the Navigation Pane, right-click the form or report that you want to change, and then select Design View on the shortcut menu.
2. On the Form Design or Report Design tab, in the Controls group, select Text Box.
3. Drag the pointer on the form or report to create the text box.
4. Right-click the text box, and then select Properties on the shortcut menu.
5. In the property sheet, select the Data tab.
6. Change the value in the Control Source property box to =[FirstName] & " " & [LastName].
7. Close the property sheet and save your changes.

Sometimes, one of the fields that you want to combine doesn't contain a value. This absence of data is called a null value. When you use the & operator on a field that has no value, Access returns a zero-length string for that field. For example, if an employee record has only a last name, the expression in the preceding example returns a zero-length string for the FirstName field, a space, and the value in the LastName field.

1. Because there is no data in the FirstName field, Martinez is preceded by a zero-length string and a space.

When you combine values from multiple fields into a new string, you might want to include a value, such as a comma, only when data exists in a particular field. To do that, use the + operator instead of the & operator to combine the fields. For example, suppose you have a table named Customer that contains fields named City, State, and Postal Code. You want to combine the values in those fields for a report, but some records might not have a value in the State field. In that case, you get an unwanted comma before the postal code if you use the & operator.

To remove the unwanted comma, use the + operator, as shown in the following expression:

=([City] & (", " + [State]) & " " & [PostalCode])

The + operator combines text in the same way as the & operator. However, the + operator also supports null propagation. Null propagation means that if any component of an expression is null, the result of the entire expression is also null. In the preceding example, consider the (", " + [State]) part of the expression. Because the + operator is used, the expression inside the inner parentheses includes a comma only if a value exists in the State field. If a value doesn't exist in the State field, null propagation takes effect, and the expression inside the inner parentheses evaluates to a null value. That hides the comma.

1. Records that include values for states are displayed with a comma, a space, and the state abbreviation.

2. No state is specified for the record with Houston in its City field, so the result is displayed without the comma, space, or state abbreviation.

Back to top