The data type you select for each field can greatly affect the performance and functionality of your application. Several factors can influence your choice of data type for each field in your table:
The type of data that's stored in the field
Whether the field's contents need to be included in calculations
Whether you need to sort the data in the field
The way you want to sort the data in the field
How important storage space is to you
The type of data you need to store in a field has the biggest influence on which data type you select. For example, if you need to store numbers beginning with leading zeros, you can't select a Number field because Access ignores leading zeros entered into a Number field. This rule affects data such as ZIP Codes (some begin with leading zeros) and department codes.
Working with Field Properties" section of this chapter covers the Format property.
If the contents of a field need to be included in calculations, you must select a Number or Currency data type. You can't perform calculations on the contents of fields defined with the other data types. The only exception to this rule is the Date field, which can be included in date/time calculations.
You also must consider whether you will sort or index the data in a field. You cannot sort by OLE and Hyperlink fields, so don't select these field types if you must sort or index the data in the field. Furthermore, you must think about the
way you want to sort the data. For example, in a Text field, Access would sort a set of numbers in the order of their left-most character, then the second character from the left, and so on (that is, 1, 10, 100, 2, 20, 200) because Access sorts data in the Text field in a standard ASCII sequence. On the other hand, Access would sort the numbers in a Number or Currency field in ascending value order (that is, 1, 2, 10, 20, 100, 200). You might think you would never want data sorted in a standard ASCII sequence, but sometimes it makes sense to sort certain information, such as department codes, in this fashion. Access 2003 gives you the ability to sort or group based on a Memo field, but it only performs the sorting or grouping based on the first 255 characters. Finally, you should consider how important disk space is to you. Each field type takes up a different amount of storage space on your hard disk, which could be a factor when you're selecting a data type for a field.
Nine field types are available in Access : Text, Memo, Number, Date/Time, Currency, AutoNumber (known as Counter in Access 2.0), Yes/No, OLE Object, and Hyperlink. Table 2.1 summarizes the appropriate uses for each field type and the amount of storage space each type needs.
NOTE
The Hyperlink field type contains a hyperlink object. The hyperlink object consists of three parts. The first part is called the
display text ; it's the text that appears in the field or control. The second part is the actual
file path (UNC) or
page (URL) the field is referring to. The third part is the
subaddress , a location within the file or page.
The most difficult part of selecting a field type is in knowing which type is best in each situation. The following detailed descriptions of each field type and when you should use them should help you with this process.
Most fields are Text fields. Many developers don't realize that it's best to use Text fields for any numbers not used in calculations. Examples are phone numbers, part numbers, and ZIP Codes. Although the default size for a Text field is 50 characters, you can store up to 255 characters in a Text field. Because Access allocates disk space dynamically, a large field size doesn't use hard disk space, but you can improve performance if you allocate the smallest field size possible. You use the FieldSize property to control the maximum number of characters allowed in a Text field.
Memo fields can store up to 65,536 characters of text, which can hold up to 16 pages of text for each record. Memo fields are excellent for any types of notes you want to store with table data. Remember, you can sort by a Memo field under Access 2003.
You use Number fields to store data that you must include in calculations. If you must include currency amounts in calculations, or if your calculations require the highest degree of accuracy, you should use a Currency field rather than a Number field. The Number field is actually several types of fields in one because Access 2003 offers seven sizes of numeric fields. Byte can store integers from 0255, Integer can hold whole numbers from 3276832767, and Long Integer can hold whole numbers ranging from less than 2 billion to just over 2 billion. Although all three of these sizes offer excellent performance, each type requires an increasingly larger amount of storage space. Two of the other numeric field sizes, Single and Double, offer floating decimal points and, therefore, much slower performance. Single can hold fractional numbers to seven significant digits; Double extends the precision to 14 significant digits. Decimal is a numeric data type introduced with Access 2002. The Decimal data type allows storage of very large numbers and provides decimal precision up to 28 digits! The final size, Replication ID, supplies a unique identifier required by the data synchronization process.
You use the Date/Time field type to store valid dates and times. Date/Time fields allow you to perform date calculations and make sure dates and times are always sorted properly. Access actually stores the date or time internally as an 8-byte floating point number. Access represents time as a fraction of a day.
NOTE
Access reflects in your data any date and time settings you establish in the Windows Control Panel. For example, if you modify the Short Date Style in Regional Settings within the Control Panel, your forms, reports, and datasheets will immediately reflect those changes.
The Currency field type is a special type of number field you use when you are storing currency values in a table. Currency fields prevent rounding off data during calculations. They hold 15 digits of whole dollars, plus accuracy to the hundredths of a cent. Although very accurate, this type of field is quite slow to process.
NOTE
Access reflects in your data any changes to the currency format made in the Windows Control Panel. Of course, Access doesn't automatically perform any actual conversion of currency amounts. As with dates, if you modify the currency symbol in Regional Settings within the Control Panel, your forms, reports, and datasheets will immediately reflect those changes.
Access automatically generates AutoNumber field values when the user adds a record. In earlier versions of Access, counter values had to be sequential. The AutoNumber field type in Access 2003 can be either sequential or random. The random assignment is useful when several users are adding records offline because it's unlikely that Access will assign the same random value to two records. A special type of AutoNumber field is a Replication ID. This randomly produced unique number helps with the replication process by generating unique identifiers used to synchronize database replicas.
You should note a few important points about sequential AutoNumber fields. If a user deletes a record from a table, its unique number is lost forever. Likewise, if a user adds a record and cancels the action, the unique counter value for that record is lost forever. If this behavior is unacceptable, you can generate your own counter values.
TIP
As with field names, if you plan to upsize your Access database to a client/server database, you must be cognizant of the field types that you select. For example, Access exports AutoNumber fields as Long Integers. Because some non-Microsoft database servers do not support auto-numbering, you have to create an insert trigger on the server that provides the next key value. You also can achieve auto-numbering by using form-level events, but this is not desirable because the database engine will not enforce the numbering if other applications access the data. If you are upsizing to Microsoft SQL Server, the Upsizing Wizard for Access 2003 converts all AutoNumber fields to Identity fields (the SQL Server equivalent of AutoNumber).
You should use Yes/No fields to store a logical true or false. What's actually stored in the field is -1 for Yes, 0 for No, or Null for no specific choice. The display format for the field determines what the user actually sees (normally Yes/No, True/False, On/Off, or a third optionNullif you set the TripleState property of the associated control on a form to True). Yes/No fields work efficiently for any data that can have only a true or false value. Not only do they limit the user to valid choices, but also they take up only one bit of storage space.
Microsoft designed OLE Object fields to hold data from any object linking and embedding (OLE) server application registered in Windows, including spreadsheets, word processing documents, sound, and video. There are many business uses for OLE Object fields, such as storing resumes, employee reviews, budgets, or videos. However, in many cases, it is more efficient to use a Hyperlink field to store a link to the document rather than store the document itself in an OLE Object field.
You use Hyperlink fields to store uniform resource locator addresses (URLs), which are links to Web pages on the Internet or on an intranet, or Universal Naming Convention paths (UNCs), which are links to a file location path. Access breaks the Hyperlink field type into three parts:
What the user sees
The URL or UNC
A subaddress, such as a range name or bookmark
After the user places an entry in a Hyperlink field, the entry serves as a direct link to the file or page it's referring to. I cover the Hyperlink field type in more detail later in this chapter, in the section "Access Tables and the Internet."