Using Data Types Wisely

In Access, every field in a table is assigned a data type. You got a glimpse of data types in Chapter 5, "Building Your First Tables," when you used table design view to build the Plants table. Now it's time to understand more about this aspect of table design.

The data type of a field controls the type and amount of data you can store in that field. This is an important part of keeping your data safe. For example, suppose you build a table that keeps track of your friends and their anniversary dates. That table might have a field for name and a field for anniversary dates. A good design for this table would use a field with the data type of date/time (usually just called a date/time field for short) to store anniversary dates. The data type would limit the field to just storing dates; if you tried to put anything else there (such as accidentally typing a name instead of a date), Access would warn you that the data cannot be saved to that field.



You can download the Chapter 11.mdb sample file for this chapter from If you want to follow along with our examples, download Chapter 10.mdb .

Figure 11.1 shows the Plants table open in table design view. As you can see, Access supports nine data types (in addition to the Lookup Wizard, which you learned about in Chapter 6, "Tapping the Power of Relationships").

Figure 11.1. The variety of Access data types.


Here's a quick explanation of the available data types and what you can use them to store:

  • Text The text data type is suitable for storing any sort of textual information ( letters , numbers , punctuation, or symbols). But there's a limit to the text data type: A single text field can store no more than 255 characters of data.

  • Memo You can think of the memo data type as a nearly unlimited text data type. Like the text data type, the memo data type can store any kind of textual information. But a memo field can hold more than 65,000 characters of information entered from the user interface. Why not just use memo fields for everything? Because they're a bit slower than text fields.

  • Number The number data type can store only numeric data. This might be a simple integer value, such as 37, or a value with a decimal point, such as 42.1857392.

  • Date/Time The date/time data type can store dates, times, or both.

  • Currency The currency data type is meant to store currency values. It's similar to a number data type, except that it's limited to four digits after the decimal point. Also, the currency data type is designed to avoid rounding errors, so that calculations involving money will be precise.

  • AutoNumber AutoNumber is a special variant of the number data type. An autonumber field is filled in by Access, not by the user. This is useful when you want to generate a unique key for a record but don't have a good key in the data.

  • Yes/No The yes/no data type can store yes or no . It's designed to keep track of simple on/off information.

  • OLE object The OLE object data type stores information in a format that is accessible to other programs, rather than to human beings. For example, the Picture field in the Plants table uses the OLE object data type. You can store any type of document in an OLE object field: Word documents, Excel spreadsheets, images, music files, and so on.

  • Hyperlink The hyperlink data type is a special text field designed to store hyperlinks to Web sites and other Internet resources.



Access stores text and memo data in a format called Unicode . This format handles accented characters, foreign alphabets such as Greek or Hebrew, and even mathematical symbols with ease. So, there's no need to worry about what kind of data your text fields can hold!

Absolute Beginner's Guide to MicrosoftR Access 2002
Absolute Beginner's Guide to MicrosoftR Access 2002
Year: 2002
Pages: 133 © 2008-2017.
If you may any questions please contact us: