SQL Operators: &

	SQL Operators: &

Description

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:

SELECT Employees.DateHired,
       Employees.LastName & ', ' & Employees.FirstName As FullName,
       Employees.Department
       FROM Employees;

Here is an example used when creating a query:

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

Employees

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;