After you name a field, you must decide what type of data the field holds. Before you begin entering data, you should have a good grasp of the data types that your system will use. Ten basic types of data are shown in Table 34-2; some data types (such as numbers) have several options.

TABLE 34-2
Data Types Available in Microsoft Access

Text
Alphanumeric characters
0–255 characters

Memo
Alphanumeric characters
0–65,536 characters

Number
Numeric values
1, 2, 4, or 8 bytes, 16 bytes for Replication ID (GUID)

Date/Time
Date and time data
8 bytes

Currency
Monetary data
8 bytes

AutoNumber
Automatic number increments
4 bytes, 16 bytes for Replication ID (GUID)

Yes/No
Logical values: Yes/No, True/False
1 bit (0 or –1)

OLE Object
Pictures, graphs, sound, video
Up to 1GB (disk space limitation)

Hyperlink
Link to an Internet resource
0–64,000 characters

Attachment
A special field (new in Access 2007) that enables you to attach external files to an Access database.
Varies by attachment

Lookup Wizard
Displays data from another table
Generally 4 bytes

Figure 34-17 shows the Data Type drop-down list used to select the data type for any field you just created.

FIGURE 34-17
The Data Type drop-down list.

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

• The data type should reflect the data stored in the field. For instance, 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 will not be performing numeric operations like addition or multiplication on phone numbers, so this data should not be stored in numeric fields. Instead, use text fields for common data such as Social Security numbers and phone numbers. Also, numeric fields never store leading zeros. Putting a zip code such as 02173 into a numericfield means only the last four digits (2173) are actually stored.
• 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.
• Will you want to sort or index the field? Because of their binary nature, memo and OLE object fields cannot be sorted or indexed. Use memo fields sparingly. The overhead required to store and work with memo fields is considerable.
• Consider the impact of data type on sorting requirements. Numeric data sort differently than text data. Using the numeric data type, a sequence of numbers will sort as expected: 1, 2, 3, 4, 5, 10, 100. The same sequence stored as text data will sort like this: 1, 10, 100, 2, 3, 4, 5. If it’s important to sort text data in a numeric sequence, you’ll have to first apply a conversion function to the data before sorting.
• Is the data text or date data? When working with dates, you’re almost always better off storing the data in a Date/Time field than as a Text field. Text values sort differently than date data (dates are stored internally as numeric values), which can upset reports and other output that rely on chronological order.
• Keep in mind the reports that will be needed. You won’t be able to sort or group memo or OLE data on a report. If it’s important to prepare a report based on memo or OLE data, add a Tag field like a date or sequence number, which can be used to provide a sorting key, to the table.

• Is Carlos Gonzalez A Product Of Coors Field? When Matt Holliday was traded away from the Rockies, I thought that perhaps the "product of Coors Field" factor was...
• Acoustic Guitar Basics The first thing you think of when you think about acoustic instruments is likely the acoustic guitar. What most people...
• Search Engine Robots Or Web Crawlers Most of the common users or visitors use different available search engines to search out the piece of information they...
• Collecting Walt Disney Collectibles One of the types of Walt Disney Collectibles that is available to you is Walt Disney Classics Collection or WDCC,...
• Search Engine Robots Or Web Crawlers Most of the common users or visitors use different available search engines to search out the piece of information they...