+ vs. &

[armsys]

What's the difference between the concatenation operators: + and &?

[HansV]

In most situations, & is the preferred operator for concatenation, since it is used for that purpose only. The + can have unexpected results because it acts as the addition operator for numbers and as the concatenation operator for strings.

But + as concatenation operator has a special property which makes it very useful in some circumstances: "text" & Null results in "text", but "text" + Null results in Null.

In the following example, single quotes have been placed around the string values for clarity; they wouldn't be present in reality.

FirstName	LastName	(FirstName & " ") & LastName	(FirstName+" ") & LastName[/b]
'John'	'Doe'	'John Doe'	'John Doe'
	'Doe'	' Doe'	'Doe'

[armsys]

Thanks for your fast help.
I learned it a long time again but forgot it recently.
The only difference: Text + NULL = NULL
Of course, the difference is important as shown in your example.
Thanks again.

[HansV]

For fields that can be a string or null, (P+" ") & Q is equivalent to the longer P & IIf(IsNull(P), "", " ") & Q
So it can help writing shorter expressions if you're concatenating several fields, some of which could be null.