Chapter 2: Creating Access Tables
53
Design your tables very conservatively, and allow for larger values than you ever expect to see in your
database. This is not to say that using the Double data type for all numeric fields is a good idea. The
Double data type is very large (8 bytes) and might be somewhat slow when used in calculations or
other numeric operations. Instead, the Single data type is probably best for most floating-point cal-
culations, and Long Integer is a good choice where decimal points are irrelevant.Date/Time data type
The Date/Time data type is a specialized number field for holding dates or times (or dates and
times). When dates are stored in a Date/Time field, it’s easy to calculate days between dates and
other calendar operations. Date data stored in Date/Time fields sort and filter properly as well.
The Date/Time data type holds dates from January 1, 100, to December 31, 9999.Currency
The Currency data type is another specialized number field. Currency numbers are not rounded
during calculations and preserve 15 digits of precision to the left of the decimal point and 4 digits
to the right. Because Currency fields use a fixed-decimal-point position, they’re faster in numeric
calculations than doubles.AutoNumber
The AutoNumber field is another specialized Number data type. When an AutoNumber field is
added to a table, Access automatically assigns a long integer (32-bit) value to the field (beginning
at 1) and increments the value each time a record is added to the table. Alternatively (determined
by the New Values property), the value of the AutoNumber field is a random integer that is
automatically inserted into new records.Only one AutoNumber field can appear in a table. Once assigned to a record, the value of an
AutoNumber field can’t be changed programmatically or by the user. AutoNumber fields are
equivalent to the Long Integer data type and occupy 4 bytes, but they display only positive val-
ues. The range of possible values for AutoNumber fields is from 1 to 4,294,967,296 — more than
adequate as the primary key for most tables.Note
An AutoNumber field is not guaranteed to generate a continuous, unbroken set of sequential numbers. For
example, if the process of adding a new record is interrupted (such as the user pressing the Esc key while enter-
ing the new record’s data) an AutoNumber field will “skip” a number. AutoNumber fields should not be used
to provide a stream of sequential numbers. Instead, sequential numbers can be easily added to a table through
a data macro (data macros are explained in Chapter 15) or VBA code.
Yes/No
Yes/No fields accept only one of two possible values. Internally stored as 1 (Yes) or 0 (No),
the Yes/No field is used to indicate yes/no, on/off, or true/false. A Yes/No field occupies a single
bit of storage.