When you want to combine the values in two or more text fields in Access, you create an expression that uses the ampersand (&) operator. For example, suppose that you have a form that is called 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. The expression also uses a pair of double quotation (") marks separated by a space character to insert a space between the first and last names. When you want to insert something between two fields, such as a space, a punctuation character, or literal text, you must enclose that additional value in quotation marks.
As another example, you can use the following expression to display 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
-
In the Navigation Pane, right-click the form or report that you want to change, and then click Design View on the shortcut menu.
-
On the Form Design or Report Design tab, in the Controls group, click Text Box.
-
Drag the pointer on the form or report to create the text box.
-
Right-click the text box, and then click Properties on the shortcut menu.
-
In the property sheet, click the Data tab.
-
Change the value in the Control Source property box to =[FirstName] & " " & [LastName].
-
Close the property sheet and save your changes.
Sometimes, one of the fields that you want to combine may not 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's record has only a last name, the expression in the preceding example returns a zero-length string for the FirstName field, a space character, 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 may want to include a value in the new string (such as a comma) only when data exists in a particular field. To conditionally include a value, you use the + operator instead of the & operator to combine your fields. For example, suppose that you have a table called Customer, and that table contains fields called City, State, and Postal Code. You want to combine the values in those fields for a report, but some records may not have a value in the State field. In that case, you end up with an unwanted comma before the postal code value if you use the & operator to combine the fields.
To eliminate the unwanted comma, you can use the plus (+) operator instead, as shown in the following sample expression:
=([City] & (", " + [State]) & " " & [PostalCode])
The + operator combines text in the same way as the & operator. However, the + operator also supports what is called Null propagation. Null propagation ensures that if any component of an expression is null, the result of the entire expression is also null. In the preceding example, consider the portion of the expression (", " + [State]). Because the + operator is used, the expression that is evaluated within the inner parentheses includes a comma only if a value exists in the State field. If a value does not exist in the State field, Null propagation goes into effect, and the expression within the inner parentheses evaluates to a null value, thus "hiding" the comma.
1. The records that include values for states are displayed with a comma, a space, and the state abbreviation.
2. There is no state specified for the record with the value Houston in its City field, so the result of the expression is displayed without the comma, space, or state abbreviation.
