Chapter 5: Using Operators and Expressions in Access
179
The addition operator (+) also concatenates two character strings. For example, to combine
FirstName and LastName from tblContacts to display them as a single string, the expres-
sion is[tblContacts].[FirstName] + “ “ + [tblContacts].[LastName]Tip
Knowing how the concatenation operator works makes maintaining your database expressions easier. If you
always use the concatenation operator (&) — instead of the addition operator (+) — when working with
strings, you won’t have to be concerned with the data types of the concatenation operands. Any expression
that uses the concatenation operator converts all operands to strings for you. Using the addition operator to
concatenate strings can sometimes lead to unpredictable results because Access must decide whether the oper-
ands are numbers or strings, and act accordingly. The concatenation operator forces Access to treat the oper-
ands as strings and always returns a string as a result.
Although the ampersand and plus sign both serve as concatenation operators, using the plus sign
might exhibit unexpected results in some situations. The ampersand always returns a string when
concatenating two values. The operands passed to the ampersand operator may be strings, numeric
or date/time values, field references, and so on, and a string is always returned.Because it always returns a string, the ampersand is often used to prevent Invalid use of null
errors when working with data that might be null. For example, let’s assume a particular text box
on an Access form may or may not contain a value because we can’t be sure the user has entered
anything into the text box. When assigning the contents of the text box to a variable (variables are
discussed in detail in Chapter 10), some developers concatenate an empty string to the text box’s
contents as part of the assignment:MyVariable = txtLastName & ““The ampersand ensures that, even if the text box contains a null value, the variable is assigned a
string and no error is raised.The plus sign, on the other hand, returns a null value when one of the operands is null:MyVariable = txtLastName + ““In this case, if txtLastName is truly null, the user may encounter an Invalid use of null
error because the result of the concatenation is null (assuming, once again, that txtLastName
contains a null value).Most experienced Access developers reserve the plus sign for arithmetical operations and always
use the ampersand for string concatenation.The Like and Not Like operators
The Like operator, and its opposite, the Not Like operator, compare two string expressions.
These operators determine whether one string matches, or doesn’t match, the pattern of another