CONCAT equivalent in MS Access
String Concatenation in MS Access: Beyond the Basics

Explore various methods for concatenating strings in Microsoft Access, from the basic &
operator to advanced VBA functions, ensuring robust data manipulation.
Microsoft Access, a powerful relational database management system, often requires users to combine or concatenate strings from different fields or literal values. Unlike some other SQL dialects that offer a CONCAT
function, Access uses its own syntax and functions for this common operation. Understanding these methods is crucial for creating dynamic queries, reports, and forms. This article will guide you through the primary ways to achieve string concatenation in MS Access, including the standard operator, built-in functions, and custom VBA solutions.
The Ampersand (&) Operator: The Standard Approach
The most common and straightforward way to concatenate strings in MS Access is by using the ampersand (&
) operator. This operator allows you to join two or more strings, including field values, literal strings, and results of expressions. It's versatile and works directly within queries, form controls, and VBA code.
SELECT FirstName & " " & LastName AS FullName
FROM Employees;
Concatenating FirstName and LastName with a space in between.
When concatenating, it's important to handle NULL
values. If any part of the concatenation is NULL
, the entire result will typically become NULL
when using the &
operator. This behavior can be problematic if you expect partial results. For instance, if LastName
is NULL
, FirstName & " " & LastName
will yield NULL
.
NULL
values when concatenating strings. If you want to treat NULL
as an empty string, use the Nz()
function, which converts NULL
to a zero-length string or another specified value.SELECT FirstName & " " & Nz(LastName, "") AS FullName
FROM Employees;
Using Nz()
to handle potential NULL
values in LastName.
Using the +
Operator (with caution)
While the &
operator is generally preferred for string concatenation, Access also allows the +
operator to concatenate strings. However, its behavior differs significantly, especially when NULL
values are involved. The +
operator performs a 'null propagation' where if any operand is NULL
, the entire result is NULL
. This is similar to how NULL
behaves in arithmetic operations.
SELECT FirstName + " " + LastName AS FullName
FROM Employees;
Concatenating strings using the +
operator.
+
operator should be used with extreme caution for string concatenation in Access. Its null propagation behavior can lead to unexpected results if not explicitly handled with functions like Nz()
for every potential NULL
operand. The &
operator is generally safer and more predictable for string-only concatenation.Advanced Concatenation with VBA Functions
For more complex scenarios, especially within VBA modules, you can leverage built-in VBA string functions or even create your own custom functions. The Join
function, for example, is excellent for concatenating elements of an array with a specified delimiter.
Function ConcatenateFields(ParamArray FieldValues())
Dim i As Long
Dim strResult As String
For i = LBound(FieldValues) To UBound(FieldValues)
If Not IsNull(FieldValues(i)) Then
strResult = strResult & FieldValues(i) & " "
End If
Next i
' Remove trailing space if any
If Len(strResult) > 0 Then
ConcatenateFields = Left(strResult, Len(strResult) - 1)
Else
ConcatenateFields = ""
End If
End Function
A custom VBA function to concatenate multiple fields, handling NULL
values and adding spaces.
This ConcatenateFields
function can then be called from a query or form control, providing a flexible way to combine an arbitrary number of fields while intelligently handling NULL
s and spacing.
SELECT ConcatenateFields([FirstName], [MiddleInitial], [LastName]) AS FullName
FROM Employees;
Using the custom VBA function in an Access query.
flowchart TD A[Start Concatenation] --> B{Choose Operator/Function} B -->|Simple Join| C["Use '&' Operator"] C --> D["Handle NULLs with Nz()"] D --> E[Result: Concatenated String] B -->|Complex Logic/VBA| F["Create Custom VBA Function"] F --> G["Define Parameters (e.g., ParamArray)"] G --> H["Implement NULL Handling & Delimiters"] H --> I["Call Function in Query/Form"] I --> E
Decision flow for string concatenation in MS Access.