Microsoft Office Tutorials and References
In Depth Information
Access Data Types
Note: Because text fields are so lax, you can obviously enter numbers, dates, and just about anything else
in them. However, you should use text only when you’re storing some information that can’t be dealt with
using another data type, because Access always treats the contents of a text field as plain, ordinary text.
In other words, if you store the number 43.99 in a text field, Access doesn’t realize you’re dealing with
numbers, and it doesn’t let you use it in a calculation.
Every text field has a maximum length. This trait comes as a great surprise to many
people who aren’t used to databases. After all, with today’s gargantuan hard drives,
why worry about space? Can’t your database just expand to fit whatever data you
want to stuff inside?
The maximum length matters because it determines how densely Access can pack
your records together. For performance reasons, Access needs to make sure that an
entire record is stored in one spot, so it always reserves the maximum amount of
space a record might need. If your table has four fields that are 50 characters apiece,
Access can reserve 200 characters’ worth of space on your hard drive for each record.
On the other hand, if your fields have a maximum 100 characters each, Access holds
onto twice as much space for each record, even if you aren’t actually using that space.
The extra space isn’t a major issue (you probably have plenty of room on your
computer), but the more spread out a database, the slower your searches.
The standard maximum length is 50, a good starting point. The box on page 721 has
some more recommendations.
To set the maximum length, go to the Field Properties section, and enter a number
in the Field Size box (Figure 26-9). The largest maximum you’re allowed is 255
characters. If you need to store a large paragraph or an entire article’s worth of
information, then you need the Memo data type instead (see the next section).
Tip: It’s worthwhile being a little generous with maximum lengths to avoid the need to modify the
Microsoft designed the Memo data type to store large quantities of text. If you want
to place a chapter from a book, an entire newspaper article, or just several
paragraphs into a field, you need the Memo data type. The name is a little odd—although
a memo field could certainly store the information from an interoffice
memorandum, it’s just as useful anytime you have large blocks of text.
When creating a memo field, you don’t need to supply a maximum length, because
Access stores the data in a memo field differently from other data types. Essentially,
it stuffs memo data into a separate section, so it can keep the rest of the record as
compact and efficient as possible, but accommodate large amounts of text.