When to use "+" vs "&"

[charles.kendricks]

In trying to learn VBA, and studying examples of code, I see both the
"+" and the "&" use to concatenate strings seemingly interchangeably
(at least to a novice, such as myself). What exactly are the rules,
implications, consequences, etc. of using one or the other?

 

[Rick Brandt]

In trying to learn VBA, and studying examples of code, I see both the
"+" and the "&" use to concatenate strings seemingly interchangeably
(at least to a novice, such as myself). What exactly are the rules,
implications, consequences, etc. of using one or the other?

The & should be your default choice. The main difference is that + propagates
Nulls whereas & does not.

"Some Text" & Null resolves to... "Some Text"

"Some Text" + Null resolves to... Null

In most cases the first result is what is desired, but there are some cases
where the second is desired and that is when you would choose the plus sign.
For example, you might be concatenating several lines of a address into a single
TextBox with line-feeds...

Address Line 1
Address Line 2
City, ST ZipCode

....would be produced by using...

[Address1] & vbCrLf & _
[Address2] & vbCrLf & _
[City] & ", " & [State] & " " & [ZipCode]

If some records do not have an Address2 entry then the above expression would
still leave a blank line. However; I don't get the blank line if I use...

[Address1] & vbCrLf & _
([Address2] + vbCrLf) & _
[City] & ", " & [State] & " " & [ZipCode]

The other issue with + is that if you use it to concatenate two string fields
that contain digits you might end up with addition being performed instead of
concatenation. The & operator would never do that.

 

[Arvin Meyer [MVP]]

In trying to learn VBA, and studying examples of code, I see both the
"+" and the "&" use to concatenate strings seemingly interchangeably
(at least to a novice, such as myself). What exactly are the rules,
implications, consequences, etc. of using one or the other?

In addition to Rick's excellent description, I'd add that you can use both
of them to achieve faster resolution in some cases. Consider the query
expression:

Expr1: [LastName] & IIf(IsNull([FirstName]), ", ","") & [FirstName]
or:
Expr1: [LastName] & NZ([FirstName]), ", ","") & [FirstName]

can also be written as:

Expr1: [LastName] & (", " + [FirstName])

This takes advantage of the propogation of nulls as well as their
non-proliferation. It not only is easier to use, it also runs faster.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Norman Yuan]

Addition to the other two posts:

If use "+", when the two expressions are the same type (Integer, or string),
then VB will do either addition or string concatenate, howerver, if the tow
are not the same type (one integer and one string), you get runtime error of
"Type mismatch" (compiling does not catch the error). While use "&", VB will
convert the non-string type to string and then do concatenating.

See this sample:

Public Sub test()

Dim i As Integer
Dim str As String

str = "CCCC"
i = 23

MsgBox i+str ''You get runtime error here
MsgBox i & str ''This line does the concatenating

End Sub

 

[david epsom dot com dot au]

+ was the original concatenation operator for strings.

& was the new concatenation operator, required as a
side effect of the implementation of the 'variant' type.

So some old examples written by people familiar with
old code have "+" instead of "&". Don't assume that
"+" is used in a code example because it is better or
more appropriate.


BTW, my favourite example of the use of & looked something
like this:

If (n & 1) Then

(david)