O'Reilly logo

Access™ 2007 VBA Programmer's Reference by Armen Stein, Geoffrey Griffith, Rob Cooper, Teresa Hennig

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required