Microsoft Office Tutorials and References
In Depth Information
Access Data Types
Word to the Wise
Changing the Data Type Can Lose Information
The best time to choose the data types for your fields is
when you first create the table. That way, your table is
completely empty, and you won’t run into any problems.
• You change the data type from Text to Number.
However, your field contains non-integer numbers
(like 4.234), and you forget to change the Field Size
property (page 724). As a result, Access assumes you
want to use only whole numbers and chops off all
your decimal places.
The best way to manage these problems is to make a
backup (page 702) before you make any drastic changes, and
to be on the lookout for changes that go wrong. In the first
two cases in the list above, Access warns you that it needs
to remove some values because they don’t fit the data type
rules (see Figure 26-8). The third problem is a little more
insidious—Access gives you a warning, but it doesn’t
actually tell you whether a problem occurred. If you suspect
trouble, switch to Datasheet view, and then check out your
data before going any further.
If you add a few records, and then decide to change the
data type in one of your fields, life becomes a little more
complicated. You can still use Design view to change the
data type, but Access needs to go through an extra step and
convert the existing data to the new data type.
In many cases, the conversion process goes smoothly.
If you have a text field that contains only numbers, you
won’t have a problem changing the data type from Text
to Number. But in other cases, the transition isn’t quite so
seamless. Here are some examples of the problems you
might run into:
• You change the data type from Text to Date, but
Access can’t interpret some of your values as dates.
• You change the data type from Text to Number, but
some of your records have text values in that field
(even though they shouldn’t).
Don’t say you weren’t warned. Here, Access
lets you know (in its own slightly obscure way)
that it can’t make the change you want—modi-
fying the data type of the field from Text to
Date—without throwing out the values in four
records. The best course of action is to click No
to cancel the change, and then to take a closer
look at your table in Datasheet view to track
down the problematic values.
Text is the all-purpose data type. It accepts any combination of letters, numbers,
and other characters. So you can use a text field for a word or two (like “Mary
Poppins”), a sentence (“The candidate is an English nanny given to flights of song.”), or
anything else (“@#$d sf_&!”).