Chapter 2: Creating Access Tables
51
l (^) Consider the impact of data type on sorting requirements. Numeric data sort differ-
ently from 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.
Tip
If it’s important to have text data representing numbers to sort in the proper order, you might want to prefix
the numerals with zeros (001, 002, and so on). Then the text values will sort in the expected order: 001, 002,
003, 004, 005, 010, 100.
l 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
from date data (dates are stored internally as numeric values), which can upset reports
and other output that rely on chronological order.
Don’t be tempted to store dates in one Date/Time field and time in another Date/Time
field. The Date/Time field is specifically designed to handle both dates and times, and,
as you’ll see throughout this book, it’s quite easy to display only the date or time portion
of a Date/Time value.
A Date/Time field is also meant to store a discrete date and time, and not a time inter-
val. If keeping track of durations is important, you should use two Date/Time fields —
one to record the start and the other at the end of a duration.
l (^) 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.
Text data type
The Text data type holds information that is simply characters (letters, numbers, punctuation).
Names, addresses, and descriptions are all text data, as are numeric data that are not used in a cal-
culation (such as telephone numbers, Social Security numbers, and zip codes).
Although you specify the size of each Text field in the property area, you can enter no more than
255 characters of data in any Text field. Access uses variable length fields to store text data. If you
designate a field to be 25 characters wide and you use only 5 characters for each record, then only
enough room to store 5 characters is used in your database.
You’ll find that the .accdb database file might quickly grow quite large, but text fields are not the
usual cause. However, it’s good practice to limit Text field widths to the maximum you believe is
likely for the field. Names can be quite tricky because fairly long names are common in some cul-
tures. However, it’s a safe bet that a postal code might be less than 12 characters, while a U.S. state
abbreviation is always 2 characters. By limiting a Text field’s width, you also limit the number of
characters users can enter when the field is used in a form.