How to Decide Which Datatype to Use in a Microsoft Access Table Field
The default datatype for a Microsoft Access field is text, but you can specify others. Choosing the most specific datatype ensures that you will get the results you want from your queries and searches. These instructions apply to MS Access 97.
Other People Are Reading
Instructions
-
-
1
Use Text if your field will contain text (letters) or a combination of letters and numbers, or numbers that are not currency or will never be calculated. For example, you could select a text datatype for a field that will contain addresses and street numbers or telephone numbers. You can sort text fields, but they sort alphabetically, not numerically.
-
2
Use Currency if your text field will contain money or if you want a high degree of accuracy in your calculations. Currency data fields calculate to 15 decimal places. Currency fields sort numerically and can be used to perform calculations.
-
-
3
Use the Memo datatype to enter lengthy notes or numbers. Memo datatype fields cannot be used to sort or index records or to perform calculations.
-
4
Use the Number datatype to enter numbers that can be sorted numerically, indexed, or calculated, and when a high degree of accuracy is not important. Calculations performed in a Number datatype field will be rounded off. For example, you might use a Numbers datatype to record the number of CDs you have for each recording artist.
-
5
Use the Date/Time field for entering dates and times. Entering date and time data in a text field is possible, but sorts will not work properly unless the field is designated as a Date/Time field.
-
6
Use the Yes/No field to force an answer of Yes/No, True/False or Off/On. For example, with a table containing data about your houseplants, you might create a field that indicates whether or not a plant is poisonous. A Yes/No field would allow you to enter only yes or no, and MS Access could then respond to a query by giving you the number of plants that are poisonous (yes) or are not (no).
-
7
Use the AutoNumber field to assign sequential or random numbers to a field. These numbers are used as primary keys and are necessary to establish relationships (shared fields) between two or more tables. You can assign the primary keys manually if you require customized identifiers, but using AutoNumber will save you some work.
-
8
Use the OLE Object field to link or embed an object from another compatible program. For example, you might link to a chart from Excel, a Word document, or a picture, sound, or video file. OLE Object fields cannot be sorted, calculated, indexed or grouped.
-
9
Use the Hyperlink field to jump to a different location in your database, to another file on your computer or LAN, or to an Internet Web site. For example, in your customer records, you might include the company's Web site address in this field. If you have a live Internet connection, clicking on this URL would launch your browser and open the Web site. Hyperlink datatype fields cannot be calculated, indexed, grouped or sorted.
-
10
Use the Lookup Wizard to create a field that will allow you to choose a value from another table or from a list of values. For example, if you have a table containing a list of items that you stock in your store, you might want to look up all the companies that supply you with one of these items - widgets. The Lookup Wizard would allow you to get the names of your widget suppliers from your Suppliers table and display these names in your Items table.
-
1
Tips & Warnings
If you make changes to the Regional date and time settings in the Windows control panel, these changes will automatically be reflected in the Date/Time field.
In a text field, numbers sort as strings of characters, not as numeric values. Although MS Access will allow you to enter numbers in a text field, it is not a good choice for numbers that you want to sort numerically.
