Data Types

The data type is a characteristic you designate for an Access field when you create it. It determines what kind of data can be entered into the field. Data types are determined primarily by the nature of the data and the amount of data that can be stored in a particular field.

The selection of data types and how the different types are variously used will make more sense when you start building tables. All data types receive much more treatment in Chapter 5, "Building Tables." But I introduce them now because it gives you an excellent idea of the kinds of data you can enter and manipulate in Access.

Let's say you're a big fan of the Beatles. You want to create a database of their music, personnel, and activities, as well as the CDs, DVDs, and memorabilia you own. You'll include things such as their biographies, song lyrics, and top websites. The following discussion describes the various data types you might use and the kinds of items included in each type.


Text is the most common data type. It is a string of alphanumeric charactersthat is, letters of the alphabet and numbers. You can include strings of up to 255 characters in a field with the Text data type.

The names of the Fab FourJohn Lennon, Paul McCartney, and so onare Text. The names of their wivessuch as Yoko and Lindaare also Text. Their albumsRubber Soul, Abbey Roadare Text, and so are their songsLet It Be, Yesterday, Norwegian Wood (This Bird Has Flown).


You use the Number data type to store numeric data that will be used in calculations.

Suppose the Classic Rock Music Store orders nine CDs of Sergeant Pepper's Lonely Hearts Club Band for its inventory. The 9 can be added to the 18 the store purchased last month and the 87 bought back in January to find the total number of Sergeant Pepper CDs purchased for the year. You use the Number data type to store data about the number of CDs in stock.

On the other hand, Beatles fans will recall that the last song on The White Album is "Revolution 9," where John gets to repeat his favorite digit over and over: "number 9, number 9, number 9, number 9…."

The 9s in both the song title and the lyrics represent a completely different use for 9 than the number of Sergeant Pepper CDs purchased. The 9s for the song are Text because you have no reason to perform arithmetic operations with them. The same logic applies to phone numbers and ZIP codesthey're Text.


A field with the Number data type accepts only numbersno letters, dashes, ampersands, and so forth are allowed.

When you select a Number data type, you have many other choices to consider. For example, should the field contain only integers (that is, whole numbers) or numbers with decimal places? If you do include decimal places, how are they stored and how are they displayed? These issues are considered in Chapter 5.


Another data type available for the entry of numeric data is Currency. You use this data type to record, say, the $69.50 you paid for a Revolver LP album in good condition, or the $1,150 it cost you for an original Yellow Submarine movie poster. An advantage of the Currency data type is that it avoids rounding errors and can match values to the penny.


The Date/Time data type offers several advantages for entering dates and times. First, it makes it easy to convert dates from one format to another. Thus, a date displayed as 12/8/80 can be easily made to appear as December 8, 1980. Second, using the Date/Time type enables you to find records that occurred during a specific time period, such as between 11/10/04 and 01/02/05, or after 8/10/05.

This data type also enables you to do calculations on your data. For example, suppose your company has a policy that all orders must be shipped within seven days of order placement. You could easily add 7 to each of a list of dates to find the deadlines.


The difference between a Text field and a Memo field is primarily a matter of size. Text fields can store a maximum of 255 characters. If you wanted to include all the lyrics to the song "Revolution 9" in a Text field, you might get to the line "Then there's this Welsh Rarebit wearing some brown underpants." But after that, Access would not allow you to enter any more lyrics. To make sure you can fit in "block that kick!" repeated 16 times, you'd want to use a Memo field, which stores up to 65,536 characters.


The Yes/No field contains only two possible values: Yes (or true) and No (or False). You use this data type to state that a condition either does or does not exist. For example, the Beatles Memorabilia Company might have an Overnight field in a products table that indicates whether an item can be shipped overnight. If it can be shipped overnight, the value is Yes; if not, the value is No. There are no maybe, perhaps, or it depends values in a Yes/No field.

OLE Object

Suppose you want to include in your database an image of Ringo's autograph, or a sound file of the song "Lady Madonna," or a Word document containing the letter you wrote Yoko in 1992. All of these are objects created in another program that can be stored in an Access database. An OLE object can be as large as a gigabyte (assuming, of course, you have all that room on your hard disk to store it).


OLE objects can be either embedded or linked. The differences between these methods are discussed in Chapter 12, "Form/Report Design Elements."


Hundreds, probably thousands, of websites are devoted to the Beatles. You can use a hyperlink to store their addresses. You can also use a hyperlink to store a path to a file on your hard drive or network. If the hyperlink is for a website, clicking it opens the site in your default browser; if the path is for a file, it opens in its associated program.

A hyperlink address has three parts, each of which can contain up to 2,048 characters:

  • Text to display The text that appears in the field

  • Address The path to a file or a page

  • Subaddress A location within the file or page

Pop Quiz on Data Types

Fauna Emporia is a small chain of pet shops that sells exotic pets and supplies. It uses several tables to store data about its customers, products, suppliers, and other subjects. Match each kind of data to its most likely data typeText, Number, Currency, Date/Time, Memo, Yes/No, OLE Object, or Hyperlink.


Customer salutation (Mr., Mrs., Ms.)


Supplier names


Whether a customer is a nonprofit organization and, therefore, tax-exempt


The words to the song "You Light Up My Life," which Oliver the parrot knows by heart


A sound file of Oliver singing "You Light Up My Life"


How many white mice you have available for sale


The list price for Aloysius the aardvark


The address of the website of Professor Robin Swan, linguist and bird authority


The suite number of the chain's feed supplier


The date Cleopatra the Persian cat had kittens











OLE Object











Hands-On Microsoft Access(c) A Practical Guide to Improving Your Access Skills
Hands-On Microsoft Access: A Practical Guide to Improving Your Access Skills
ISBN: 0321245458
EAN: 2147483647
Year: 2005
Pages: 169
Authors: Bob Schneider © 2008-2017.
If you may any questions please contact us: