Joining two strings of text together in Access

Applies to
Microsoft Office Access 2003
Microsoft Access 97, 2000 and 2002

You can join two strings of text together by using the & operator in an expression. This is called concatenation. For example, the result of both of the following expressions is the text string Hello world!

"Hello " & "world!"

"Hello" & " " & "world!"

You can use concatenation to combine the text in two or more fields into a single text string, and you can display the result in a field in a query, or in a control on a form or report.

For example, if you have a table that contains the fields FirstName and LastName, you can use the following expression to create a text string that displays the values of the FirstName and LastName fields separated by a space.

[FirstName] & " " & [LastName]

To see how you can use this expression in a form, open the Northwind sample database, and then open the Employees form in Design view.

ShowI can't find the Northwind sample database

The default folder location of the Northwind sample database is as follows, for these versions of Access:

  • Access 2003    \Program Files\Microsoft Office\Office11\Samples
  • Access 2002    \Program Files\Microsoft Office\Office10\Samples
  • Access 97 and 2000    \Program Files\Microsoft Office\Office\Samples

Note that the & operator appears in the text box in the form header. Also, note that in a calculated control on a form or report, you precede the expression with the = operator. Switch to Form view and scroll through the records to see the results of the expression.

To see another example of concatenation in the Northwind sample database, open the Customer Labels report in Design view. The following expression in the Address2Line box uses the & operator to combine values from three fields - City, Region, and PostalCode - into a single string of text.

=Trim([City] & " " & [Region] & " " & [PostalCode])

In this expression, the Trim function removes any leading or trailing spaces.

Applies to:
Access 2003