Microsoft Office Tutorials and References
In Depth Information
Manipulating Text with Expressions
Manipulating Text with Expressions
You can use the contents of Text fields (also called strings, which is short for
“a string of characters”) in expressions as well, but adding, subtracting,
multiplying, and dividing with strings doesn’t make sense. After all, Smith times
Jones or Smith divided by Jones makes no sense at all. Instead, you can
use the ampersand (&) operator to concatenate (join) strings.
The expression [First Name] & [Last Name], for example, joins the
contents of the Last Name and First Name fields. If the Last Name field
contains Pines, and the First Name field contains Tori, the expression
[First Name] & [Last Name] returns ToriPines.
Adding spaces to text expressions
“But wait,” you say. “Shouldn’t that be Tori Pines, with a space in between?”
To you and me, it should be — but that’s not what the expression says.
The expression says, “Stick the First Name value and Last Name value
together.” It doesn’t add, “And put a space between them.” Computers are
literal-minded, so you can fix the problem easily by using literal text.
Literal text is any text that doesn’t refer to a field name, a function, or
anything else that has special meaning to Access. To use literal text in a
calculated field expression, enclose the text in quotation marks. A blank space is
a character — a chunk of literal text. Watch what happens if you rewrite the
previous example expression like this:
[First Name] & “ “ & [Last Name]
The result is Tori Pines with a space in between. The expression says,
“Display the contents of the First Name field, followed by a blank space,
followed by the contents of the Last Name field.”
Two quotation marks right next to each other, with no blank space between
them, is a zero-length string, which is basically nothing at all. [First Name]
& “ ” & [Last Name] returns something like Tori Pines, and the
expression [First Name] & “” & [Last Name] returns something like
ToriPines (the first and last names with nothing in between).
Suppose that a table contains City, State, and ZIP fields. The following
expression displays the city name followed by a comma and a blank space,
followed by the state name, followed by two blank spaces, followed by the
zip code:
[City] & “, “ & [State] & “ “ & [ZIP]
An example of the preceding expression might look something like this:
Los Angeles, CA 91234
Search JabSto ::




Custom Search