Microsoft Access 2010 Bible

Part I: Access Building Blocks

178

String operators

Access has three string operators for working with strings. Unlike the mathematical and logical

operators, the string operators are specifically designed to work with the string data type:

& Concatenates operands

Like Operands are similar

Not Like Operands are dissimilar

The concatenation operator: &

The concatenation operator joins two or more strings into a single string. In some ways, concatena-

tion is similar to addition. Unlike addition, however, concatenation always returns a string:

[FirstName] & [LastName]

However, there is no space between the names in the returned string. If [FirstName] is “Fred”

and [LastName] is “Smith,” the returned string is FredSmith. If you want a space between the

names, you must explicitly add a space between the strings, as follows:

[FirstName] & “ “ & [LastName]

The concatenation operator easily joins a string with a numeric- or date-type value. Using the &

eliminates the need for special functions to convert numbers or dates to strings.

Suppose, for example, that you have a number field (HouseNumber) and a text field

(StreetName), and you want to combine both fields:

[HouseNumber] & “ “ & [StreetName]

If HouseNumber is “1600” and StreetName is “Pennsylvania Avenue N.W.,” the returned

string is

“1600 Pennsylvania Avenue N.W.”

Note
Quotes are added around the returned string to clarify the result.

Maybe you want to print the OperatorName and current date at the bottom of a report page.

This can be accomplished with the following:

“This report was printed “ & Now() & “ by “ & [OperatorName]

Notice the spaces after the word printed and before and after the word by. If the date is March 21,

2012, and the time is 4:45 p.m., this expression looks like:

This report was printed 3/21/12 4:45:40 PM by Jim Rosengren