Field Types and Uses in Access 2019 Databases

A field in Access 2019, you remember, is where your data lives. Each field holds one piece of data, such as Last Name or Batting Average. Because there are so many different kinds of information in the world, Access offers a variety of field types for storing it. In fact, Access puts the following field types at your disposal:

• 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

The types just listed are those available for fields you create in addition to that first field — the ones that will contain your data.

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.

To help you start thinking about your database and your data and to begin imagining the fields you could use for some common types of data, this table presents a breakdown of field types and ways you might use them.

Common Fields for Everyday Tables

All the field types listed as samples in this table are really text fields, even the ones for phone numbers. This is because Access sees their content as text rather than as a number that could be used in a calculation. (Check out the following table for field-naming no-nos.)

Prohibited Symbols

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.