What is the difference between '&' - '+' with concatenation

D

Douglas J Steele

Using &, if both expressions are Null, result is Null. However, if only one
expression is Null, that expression is treated as a zero-length string ("")
when concatenated with the other expression.

Using +, however, if either of the expressions is Null, the result will be
Null as well.

In other words, "X" & Null results in "X". "X" + Null results in Null.
 
I

Isis

Using &, if both expressions are Null, result is Null. However, if
only one expression is Null, that expression is treated as a
zero-length string ("") when concatenated with the other expression.

Using +, however, if either of the expressions is Null, the result
will be Null as well.

In other words, "X" & Null results in "X". "X" + Null results in Null.
Click to expand...

Thanks for that Doug.

Regards
 
J

Jerry Whittle

Be careful with numbers as things can get strange:

Debug.Print 1 & 2 = 12

Debug.Print 1 + 2 = 3

Debug.Print "1" + "2" = 12

Debug.Print "1" + 2 = 3

Debug.Print 1 + "2" = 3

Debug.Print 1 + "Two" returns a type mismatch error.

Debug.Print 1 & "Two" = 1Two
 
I

Immanuel Sibero

So is it fair to say that the general rule of thumb is when it comes to
string concatenation, stick with & and avoid using + as it may produce
unexpected result?

Immanuel Sibero
 
D

Douglas J Steele

Yes, unless you want to use the + operator to eliminate unnecessary
characters.

For example, if you've got fields City and State, but not all of the entries
have states, you could use the following to ensure that you don't have a
comma after those cities that don't have a state:

= City & (", " + State)
 
J

John Spencer

If you understand the differences in the way the concatenation is handled,
you can get some nice results. For instance, you often see the combination
of both types of concatenation used when you want to build an address where
some of the fields may be null

AddressLine1 & (Chr(13) + Chr(10) + AddressLine2) & Chr(13) & Chr(10) & City
& ", " & State & " " & PostalCode

That will give you two or three line addresses depending on whether or not
AddressLine2 has values. You can get the same effect by using and IIf
statement)

AddressLine1 & IIF(AddressLine2 Is Not Null,Chr(13) & Chr(10) &
AddressLine2, "") & Chr(13) & Chr(10) & City & ", " & State & " " &
PostalCode
 
B

BillCo

Nice one! Never even thought of that usage
....to think of all the lenghty iif(isnull( type statements if
unnecessarily written!!!
 
D

Douglas J. Steele

My advise, though, is at least put a comment in the code so that others can
figure out what you're doing.

= IIf(IsNull([Field2]), [Field1], [Field1] & " and " & [Field2])

is a little easier to understand than

= [Field1] & (", " + [Field2])

(I'm a firm believer in self-documenting code)
 
Top