Concatenating Strings in VBA: Plus (+) vs. Ampersand (&)

VBA has two string concatenation characters:

• &: Ampersand
• +: Plus sign

Both operators can be used to concatenate two string, but there are key differences in how they behave when dealing with certain data types.

• Ampersand (&) coerces Null to a zero-length string
• Anything plus (+) Null evaluates to Null
• Ampersand (&) treats numbers-as-text (e.g., "1") as strings
• Plus (+) will coerce numbers-as-text (e.g., "1") to numbers

These behaviors allow Access developers to use clever tricks, but can also lead to subtle bugs.

The FirstName LastName Trick

The most common of these tricks is joining an optional FirstName field to a required LastName field.

The goal, in general, is to join an optional field to a required field using a delimiter.  The trick is that the delimiter is only included if both fields have values.  It's easier to demonstrate if we use the "LastName, FirstName" format so that the delimiter (", ") is easier to see if one of the values is missing.

To Make This Trick Work

• Use the ampersand when joining the required field to the delimiter
• Use the plus sign when joining the optional field to the delimiter

?"Wolfe" & ", " + "Mike"
Wolfe, Mike

?"Wolfe" & ", " + Null
Wolfe

When both values are present, the delimiter appears as a separator between them.

When the optional value is missing, the ", " + Null expression evaluates to Null.  In the next step of the evaluation, "Wolfe" & Null evaluates to "Wolfe".

Position of the Operators is Important

It's important that you get the operators in the right spots, otherwise you get undesirable results:

As I alluded to earlier, this technique does open the door to some subtle bugs that can be hard to find.  In particular, it can be tricky to sort out how the plus sign will deal with expressions that involve numbers, nulls, and strings that look like numbers (e.g., "1").

Additionally, this technique's reliance on relatively obscure behaviors of VBA can cause confusion among newer developers and–in some cases–lead to new developers "fixing" these expressions by switching to a single operator for the sake of consistency, without understanding the consequences of that action.

The remainder of this article includes examples of the various scenarios so that you can see how VBA handles common combinations of data types.

Examples

Each of the following examples uses the same basic test routine.  

The function demonstrates six different scenarios:

• Direct concatenation via &
• Direct concatenation via +
• Joining the two values to either side of a string using &
• Joining the two values to either side of a string using & then +
• Joining the two values to either side of a string using + then &
• Joining the two values to either side of a string using +

Only the first two lines of code vary among the examples.

Here's the base routine:

Sub TestTwoStrings()
    Const x As String = "1"
    Const y As String = "2"
    
    On Error GoTo HandleErr
    Debug.Print TypeName(x); ": "; x
    Debug.Print TypeName(y); ": "; y

    Debug.Print "--== Direct concatenation ==--"
    Debug.Print "&: "; x & y
    Debug.Print "+: "; x + y
    Debug.Print
    
    Debug.Print "--== Joined with a hyphen ==--"
    Debug.Print "&&: ";: Debug.Print x & "-" & y
    Debug.Print "&+: ";: Debug.Print x & "-" + y
    Debug.Print "+&: ";: Debug.Print x + "-" & y
    Debug.Print "++: ";: Debug.Print x + "-" + y
    Debug.Print

    Exit Sub
HandleErr:
    Debug.Print "ERROR " & Err.Number & ": " & Err.Description
    Resume Next
End Sub

Two Strings

Sub TestTwoStrings()
    Const x As String = "1"
    Const y As String = "2"

Two Numbers

Sub TestTwoNumbers()
    Const x As Long = 1
    Const y As Long = 2

String and Number

Sub TestStringAndNumber()
    Const x As String = "1"
    Const y As Long = 2

Number and String

Sub TestNumberAndString()
    Const x As Long = 1
    Const y As String = "2"

Two Nulls

Sub TestTwoNulls()
    Const x As Variant = Null
    Const y As Variant = Null

Null and String

Sub TestNullAndString()
    Const x As Variant = Null
    Const y As String = "Last"

String and Null

Sub TestStringAndNull()
    Const x As String = "First"
    Const y As Variant = Null

Null and Number

Sub TestNullAndNumber()
    Const x As Variant = Null
    Const y As Long = 2

Number and Null

Sub TestNumberAndNull()
    Const x As Long = 1
    Const y As Variant = Null