Tips for Choosing Field Types in Access 2010

By Alison Barrows, Margaret Levine Young, Joseph C. Stockman

Part of Access 2010 All-In-One For Dummies Cheat Sheet

When you design a database in Access 2010, you decide what type each field will be. Here are tips for when to use which type of field.

Field Type What It Holds
Text Text up to 255 characters long (including spaces and
punctuation). Use a Text field, not a Number field, for codes
— such as phone numbers, ZIP codes, and other postcodes
— even if they look like numbers.
Memo Like a Text field, but more of them — up to 65,536
characters. A memo field can contain rich (formatted) text, and you
can set it to Append Only, so that it can accumulate text notes,
without allowing the user to delete what’s already
there.
Number Only numbers. You may use + or – before the number, and a
decimal point. If you plan to do math with a field, use a Number or
Currency field.
Currency Numbers with a currency sign in front of them ($, ¥, and so
on).
AutoNumber Numbers unique to each record and assigned by Access as you add
records, starting at 1. Use an AutoNumber field as the primary key
field for most tables.
Date/Time Dates, times, or both.
OLE Object Object Linking and Embedding. Don’t use it when creating
a new database; use the new Attachment type instead because it
stores data more efficiently.
Hyperlink This text string is formatted as a hyperlink. (If you click the
link, it takes you to the page.) This is especially useful if
there’s related information on the Web.
Yes/No Yes or no (a particular condition is, or isn’t, in
effect) — or other two-word sets, such as True/False, On/Off, or Male/Female. Use a
Yes/No field if you want to display the field as a check box on
forms.
Attachment You can store one or more entire files — pictures, sound,
Word documents, even video — in one Attachment
field.
Calculated You enter a formula that Access uses to calculate the value of
this field based on other fields in the table. Use a Calculated
field when a calculated value will be used in many queries, forms,
and reports.