> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Concatenating Strings in VBA: Plus (+) vs. Ampersand (&)
- URL: https://nolongerset.com/plus-vs-ampersand/
- Published: 2024-01-01T20:44:16.000Z
- Updated: 2026-05-08T12:37:47.000Z
- Description: Both the ampersand and the plus sign operators can be used to join strings in VBA. Clever developers can use this to their advantage.
- Author: Mike Wolfe
- Tags: Hidden Features, VBA, Intermediate, #Import 2026-05-20 02:59

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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/01/image-9.png)

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:

```vba
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

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/01/image.png)

### Two Numbers

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/01/image-1.png)

### String and Number

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/01/image-2.png)

### Number and String

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/01/image-3.png)

### Two Nulls

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/01/image-4.png)

### Null and String

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/01/image-5.png)

### String and Null

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/01/image-6.png)

### Null and Number

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/01/image-8.png)

### Number and Null

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/01/image-7.png)