Database Reference
In-Depth Information
cleanup before you can use it. This data has people's first and last names in the same field;
it's even got entire email messages crammed into a field—address, subject, and body—when
all you need is the email address. You can equip a temporary database with fields and text
calculations to
parse
(think of it as sifting) the data into the form your better-designed data-
base expects.
NOTE
Fixing data with parsing calculations is called a
calculated replace
, since you use the Replace dialog
box to enter the calculation. Since Replace works on the found set of records, you do a find first to
isolate just the records that have the problem you're fixing and then (and only then) do a calculated
Replace Field Contents that fixes the error.
In contrast to the wide variety of mathematical operators for working with numbers, only one
pertains specifically to text—the
concatenation
operator. Represented by the “&” sign (am-
persand), it strings bits of text together. (When you need to chop and divide your text in or-
der to parse it, you use a
function
instead of an operator, as described in the next section.)
To use this operator, put it between units of text, as in the expression below:
"This is a " & "test"
The result of this calculation is
This is a test
.
The concatenation operator lets you combine text from two different fields and make them
work better together. Although you store First Name and Last Name in two separate fields,
you often want to display them in a single field. When you're showing a second field in a
value list, you can pick only
one
field to show along with the primary value. That example
used the First Name field, but the full name would make the menu more useful. That's where
concatenation comes in.
Create a new calculation field in the Customers table called
Full Name
. Set the calculation's
result type to Text and then create this calculation:
Last Name & ", " & First Name
Some results are “Greystone, Daniel” or “Adama, Joseph.” Note that the calculation includes
a comma and the appropriate spaces for separating data between your fields. Now you can
use the Full Name field instead of First Name in a value list.
Figure 10-3
shows the result.
