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

[Isis]

Is there a difference in the results when using either of these ?

Thanks

 

[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.

 

[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.

Thanks for that Doug.

Regards

 

[Guest]

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

 

[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

 

[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)

 

[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

 

[Immanuel Sibero]

That's a new one for me! Thanks Doug.

Immanuel Sibero

 

[Immanuel Sibero]

As my response to Doug above.. a nice one.. thanks John.

Immanuel Sibero

 

[BillCo]

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

 

[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)

 

[Isis]

As my response to Doug above.. a nice one.. thanks John.

Immanuel Sibero

This seems to have shed a great deal of light on the subject !

Thanks to all