Microsoft Access SQL Operators: &

	SQL Operators: &

Introduction

The ampersand operator &, is used to concatenate two strings, a string and the name of a field, or two fields, to get a new expression.

Examples

Here is an example:

Private Sub Form_Load()
    RecordSource = "SELECT EmployeeNumber, " & _
                   "       Employees.DateHired, " & _
                 "Employees.LastName & ', ' & Employees.FirstName As FullName, " & _
                   "       Employees.DepartmentID " & _
                   "FROM Employees;"
End Sub

Employees

Here is an example used when creating a query:

Employee: [FirstName] & " " & [MiddleName] & " " & [LastName]

The equivalent SQL statement is:

SELECT Employees.DateHired,
       [FirstName] & " " & [MiddleName] & " " & [LastName] AS Employee
FROM   Employees;

Here is an example that uses an IIf() function:

SELECT Employees.DateHired,
       IIf(IsNull([MiddleName]),
       [FirstName] & " " & [LastName],[FirstName] & " " & 
       UCase(Left([MiddleName],1)) & " " & [LastName]) AS Employee
FROM Employees;