- Short Text
- Long Text
- Number
- Currency
- AutoNumber (this data type is applied, by default, to the starting ID field in any new table)
- Date/Time
- Yes/No
- Lookup & Relationship
- Rich Text
- Attachment
- Hyperlink
- OLE Object
- Calculated
- Lookup Wizard
For now, suffice it to say that the aforementioned AutoNumber field is a field that contains an automatically generated number so that each record is unique in that it has a unique AutoNumber, or ID.
For now, don’t worry about figuring out what each field type is or what it does based on its name — I go over each one shortly. As you can see, though, the list covers just about any type of data you can imagine. And remember, each one can be customized extensively, resulting in fields that meet your needs exactly.
The upcoming bulleted list introduces the available field types and how they’re used. You’ll also find out a little bit about how you can tweak them to meet your specific needs:- Short Text: Stores up to 255 characters of text — letters, numbers, punctuation, and any combination thereof.
- Long Text: This replaces the Memo field type found in versions 2010 and previous. A Long Text field holds up to 64,000 characters of information — that’s almost 18 pages of text. This is a really big text field. It’s great for general notes, detailed descriptions, and anything else that requires a lot of space.
Numbers in a text field aren’t numbers to calculate with; they’re just a bunch of digits hanging out together in a field. Be careful of this fact when you design the tables in your database — you don’t want to enter, say, a value that you intend to use in a Calculated field or to extract some other kind of information from a report and have that value stored as text, rendering it inoperable as a number. If the data is numeric, store it that way.
Text fields have one setting you need to know about: size. When you create a text field, Access wants to know how many characters the field holds. That’s the field size. If you create a field called First Name and make its size 6, Joseph fits into the field, but not Jennifer. This restriction can be a problem. A good general rule is to make the field a little larger than you think you need. It’s easy to make the field even larger at some later point if you need to, but it’s potentially dangerous to make it smaller.- Number: Holds real, for-sure numbers. You can add, subtract, and calculate your way to fame and fortune with number fields. But if you’re working with dollars and cents (or pounds and pence), use a Currency field instead.
- Currency: Tracks money, prices, invoice amounts, and so on. In an Access database, the buck stops here. For that matter, so do the lira, the mark, and the yen. If you’re in the mood for some other kind of number, check out the Number field.
- Date/Time: Stores time, date, or a combination of the two, depending on which format you use. Use a Date/Time field to track the whens of life. Pretty versatile, eh?
- Yes/No: Holds Yes/No, True/False, and On/Off, depending on the format you choose. When you need a simple yes or no, this is the field to use.
- Lookup & Relationship: If you want a field within one table to actually display content from a field in another table, choose this as the field type. A simple Lookup Wizard opens as soon as this field type is chosen, through which you select the table and field to look up through this new field in your table.
- Rich Text: Need the content of a particular field to be formatted just so? Choose this field type, and the formatting applied to the data in the field (using the Text Formatting tools on the Home tab) will be how it appears onscreen and in reports.
- OLE Object: You can use the OLE Object data type to link or embed an object — such as an Excel worksheet or Word document — to an Access table.
- Attachment: Use this field type to attach files — Word documents, Excel worksheets, PowerPoint presentations, or any other kind of file, including graphics (a photo of the volunteer, product, or location, perhaps?) — to the record.
- Hyperlink: Thanks to this field type, Access understands and stores the special link language that makes the Internet such a powerful place. If you use Access on your company’s network or use the Internet extensively, this field type is for you.
- Calculated: Use this field type when you want to fill the field in question with the result of a formula that uses one or more other fields in the same table. For example, in a table that contains a list of your products, other fields might include Price and Discount. If you want to also have a field that calculates the new price (the Price, less the Discount), you'd make that a Calculated field. When you choose this as the field type, you use a submenu to choose what kind of data will house the result, and then an Expression Builder dialog box appears, through which you set up the formula.
Common Fields for Everyday Tables
| Name | Type | Size | Contents |
| Title | Short Text | 4 | Mr., Ms., Mrs., Mme., Sir, and so on. |
| First Name | Short Text | 15 | Person’s first name. |
| Middle Initial | Short Text | 4 | Person’s middle initial; allows for two initials and punctuation. |
| Last Name | Short Text | 20 | Person’s last name. |
| Suffix | Short Text | 10 | Jr., Sr., II, Ph.D., and so on. |
| Job | Short Text | 25 | Job title or position. |
| Company | Short Text | 25 | Company name. |
| Address 1, Address 2 | Short Text | 30 | Include two fields for the address because some corporate locations are pretty complicated these days. |
| City | Short Text | 20 | City name. |
| State, Province | Short Text | 4 | State or province; apply the name appropriately for the data you’re storing. |
| Zip Code, Postal Code | Short Text | 10 | Zip or postal code; note that it’s stored as text characters, not as a number. |
| Country | Short Text | 15 | Not needed if you work within a single country. |
| Office Phone | Short Text | 12 | Voice telephone number; increase the size to 17 for an extension. |
| Fax Number | Short Text | 12 | Fax number. |
| Home Phone | Short Text | 12 | Home telephone number. |
| Cellular Phone | Short Text | 12 | Cell phone (or “mobile phone” for you cosmopolitans). |
| Email Address | Short Text | 30 | Internet email address. If the person whose record you’re building has multiple email addresses, make this one Email1, and number the alternatives — Email2, Email3, and so on. |
| Website | Hyperlink | Web page address; Access automatically sets the field size. | |
| SSN | Short Text | 11 | U.S. Social Security number, including dashes. |
| Comments | Long Text | A freeform space for notes; Access automatically chooses a field size. |
Prohibited Symbols
| Symbol | Name | |
| / | Forward slash | |
| ? | Question mark | |
| * | Asterisk | |
| - | Dash | |
| ; | Semicolon | |
| " | Double quotes | |
| : | Colon | |
| ' | Single quote | |
| ! | Exclamation point | |
| $ | Dollar sign | |
| # | Pound sign | |
| % | Percent | |
| & | Ampersand |
Of course, another field type (listed in the Type column) is neither a Short Text nor Long Text field — you also see the Hyperlink field. This data type is also considered text, but the Hyperlink data type stores URLs, as URLs — not just as a string of text and punctuation.
If all this text versus numbers stuff is confusing you, remember that computers think there’s a difference between a number (that you’d use in a calculation) and a string of digits, such as the digits that make up a phone number. When it comes to different kinds of text fields, it’s a matter of how much text will be stored in the field, and if it needs any special formatting in order to work properly in the database.