Microsoft Access 2010 Bible

Part I: Access Building Blocks

50

Figure 2.18 shows the Data Type drop-down list used to select the data type for the field you just

created.

FIGURE 2.18

The Data Type drop-down list

Here are the basic rules to consider when choosing the data type for new fields in your tables:

l The data type should reflect the data stored in the field. For example, you should

select one of the numeric data types to store numbers like quantities and prices. Do not

store data like phone numbers or Social Security numbers in numeric fields, however.

Your application won’t be performing numeric operations like addition or multiplication

on phone numbers, and this data should not be stored in numeric fields. Instead, use text

fields for common data, such as Social Security numbers and phone numbers.

Note
Numeric fields never store leading zeros. Putting a zip code such as 02173 into a numeric field means only the
last four digits (2173) are actually stored.

l Consider the storage requirements of the data type you’ve selected. Although you can

use a Long Integer data type in place of a Simple Integer or Byte Value, the

storage requirements of a Long Integer (4 bytes) is twice that of a Simple Integer.

This means that twice as much memory is required to use and manipulate the number

and twice as much disk space is required to store its value. Whenever possible, use Byte

or Integer data types for simple numeric data.

l Will you want to sort or index the field? Because of their binary nature, Memo and OLE

Object fields can’t be sorted or indexed. Use Memo fields sparingly. The overhead

required to store and work with Memo fields is considerable.