Access™ 2007 VBA Programmer's Reference

8.10. String Concatenation Techniques

Sooner or later, you'll need to join (concatenate) two strings together. The operator for performing concatenation is &. You may be tempted to say "and" when you see this symbol, but it really means "concatenate with." A classic example is joining First Name with Last Name, like this:

strFullName = FirstName & "" & LastName

This results in the First Name and Last Name together in one string, like "Tom Smith."

8.10.1. The Difference Between & and +

There are times when you may need to concatenate something to a string, but only if the string actually has a value. For example, you may want to include the Middle Initial in a person's full name. If you write code like this:

strFullName = FirstName & "" & MiddleInitial & "" & LastName

you will have a small problem. People with no middle name (Null in the table) will have two spaces between their first and last names, like this:

Tom  Smith

Fortunately, there is another concatenation operator: +. The technical explanation of this operator is "concatenation with Null propagation." That's a great phrase to impress your friends with at parties, but an easier explanation is that it concatenates two strings like the & operator, but only if both strings have a value. If either one is Null, the result of the whole concatenation operation is Null.

Using the FullName example, the goal is to have only one space separating First and Last names if there is no Middle Initial. Using +, you can tack on the extra ...