This article explains how to modify or change the data type applied to a table field in an Access database.
In this article
Understand data types
When you first design and build a database, you plan one or more tables, you plan the fields (columns) for each table, and you set a data type for each field. For example, if you need to store dates and times, you set a field to the Date/Time data type. If you need to store names and addresses, you set one or more fields to the Short Text data type, and so on.
Access provides many different data types, and each type has a specific purpose. For a full list of all supported data types across the various Access versions, see Data types for Access desktop databases and Introduction to data types and field properties.
For more information about creating and using lookup fields, see the article Create or delete a multivalued field.
Finally, data types provide a basic form of data validation because they help ensure that users enter the proper types of data in your table fields. For example, you cannot enter text in a field set to accept only numbers.
Change data types
Before you follow these steps, remember that changing a data type might truncate (cut off) some or all of the data in a field, and in some cases may remove the data entirely. For information about how Access changes data when you change a data type, see the section Restrictions on changing data types, later in this article.
Note:Â In Access, you can set the data types for your table fields by working in either Datasheet view or Design view. Steps in this section explain how to set the data type in both views. When you set data types in Design view, you see an extra choice, Lookup Wizard. That choice is actually not a data type. Instead, you use the Lookup Wizard to create lookup fields, which link foreign key fields to other tables. By default, Access sets lookup fields to the Number data type.
Change data types in Datasheet view
-
In the Navigation Pane, locate and double-click the table that you want to change.
Access opens the table in Datasheet view.
-
Select the field (the column) that you want to change.
-
On the Table Fields tab, in the Properties group, click the arrow in the drop-down list next to Data Type, and then select a data type.Â
-
Save your changes.
Change data types in Design view
-
If you have the table open in Datasheet view, right-click the document tab for the table and click Design View.
-or-
If you do not have the table open, in the Navigation Pane, right-click the table that you want to change, and then click Design View on the shortcut menu.
-
Locate the field that you want to change, and select a new data type from the list in the Data Type column.
-
Save your changes.
Restrictions on changing data types
As a rule, you can change the data type of all fields, except for:
-
Number fields with the Replication ID property enabled.
-
OLE Object fields.
-
Attachment fields.
In addition, you can change most data types when a field contains data. However, depending on the original data type and the new data type that you want to use, Access might truncate or delete some data, or it may not allow the conversion at all.
The following table lists the possible data type conversions that you can perform in Access, and describes any restrictions that Access might impose on the conversion.
|
Convert to this type |
From this type |
Changes or restrictions |
|---|---|---|
|
Short Text |
Long Text |
Access deletes all but the first 255 characters. |
|
Number |
No restrictions. |
|
|
Date and Time |
No restrictions for Date/Time. Not allowed for Date/Time Extended. |
|
|
Currency |
No restrictions. |
|
|
AutoNumber |
No restrictions. |
|
|
Yes/No |
The value -1 (Yes in a Yes/No field) converts to Yes. The value 0 (No in a Yes/No field) converts to No. |
|
|
Hyperlink |
Access truncates links longer than 255 characters. |
|
|
Long Text |
Short Text |
No restrictions. |
|
Number |
No restrictions. |
|
|
Date and Time |
No restrictions for Date/Time. Not allowed for Date/Time Extended. |
|
|
Currency |
No restrictions. |
|
|
AutoNumber |
No restrictions. |
|
|
Yes/No |
The value -1 (Yes in a Yes/No field) converts to Yes. The value 0 (No in a Yes/No field) converts to No. |
|
|
Hyperlink |
No restrictions. |
|
|
Number |
Short Text |
Short Text must consist of numbers and valid currency and decimal separators. The number of characters in the Short Text field must fall within the size set for the Number field. For more information about the sizes of number fields, see the article Create or delete a Number or Currency field. |
|
Long Text |
The Long Text field must contain only text and valid currency and decimal separators. The number of characters in the Long Text field must fall within the size set for the Number field. For more information about the sizes of number fields, see the article Create or delete a Number or Currency field. |
|
|
Number, but with a different field size or precision |
Values must not be larger or smaller than what the new field size can store. Changing precision may cause Access to round some values. |
|
|
Date and Time |
Date/Time: The dates that you can convert depend on the size of the number field. Remember that Access stores all dates as serial dates and stores the date values as double-precision floating integers. Access uses December 30, 1899 as date 0. Dates outside of the range April 18, 1899 and September 11, 1900 exceed the size of a Byte field. Dates outside the range April 13, 1810 and September 16, 1989 exceed the size of an Integer field. To accommodate all possible dates, set the Field Size property of your Number field to Long Integer or larger. For more information about serial dates and how Access uses and stores date values, see the article Format a date and time field. Date/Time Extended: Not allowed. |
|
|
Currency |
Values must not exceed (or fall below) the size limit set for the field. For example, you can convert a currency field to an Integer field only when those values are greater than 255 and do not exceed 32,767. |
|
|
AutoNumber |
Values must fall within the size limit set for the field. |
|
|
Yes/No |
"Yes" values convert to -1. "No" values convert to 0. |
|
|
Hyperlink |
Not applicable. |
|
|
Date/Time |
Short Text |
Original text must be a recognizable date or date-time combination. For example, 18-Jan-2006. |
|
Long Text |
Original text must be a recognizable date or date-time combination. For example, 18-Jan-2006. |
|
|
Number |
Value must fall between -657,434 and 2,958,465.99998843. |
|
|
Date/Time Extended |
No restrictions. |
|
|
Currency |
Value must fall between -$657,434 and $2,958,465.9999. |
|
|
AutoNumber |
Value must exceed -657,434 and be less than 2,958,466. |
|
|
Yes/No |
The value -1 (Yes) converts to December 29, 1899. The value 0 (No) converts to midnight (12:00:00 AM). |
|
|
Hyperlink |
Not applicable. |
|
|
Date/Time Extended |
Short Text |
Not allowed. |
|
Long Text |
Not allowed. |
|
|
Number |
Not allowed. |
|
|
Date/Time |
Fractional seconds are lost, and if the year is outside the range supported by Date/Time then the value is deleted. For more information, see Using the Date/Time Extended data type. |
|
|
Currency |
Not allowed. |
|
|
AutoNumber |
Not allowed. |
|
|
Yes/No |
Not allowed. |
|
|
Hyperlink |
Not applicable. |
|
|
Currency |
Short Text |
Text must consist of numbers and valid separators. |
|
Long Text |
Text must consist of numbers and valid separators. |
|
|
Number |
No restrictions |
|
|
Date and Time |
Date/Time: No restrictions, but Access may round the value. Date/Time Extended: Not allowed. |
|
|
AutoNumber |
No restrictions |
|
|
Yes/No |
The value -1 (Yes) converts to $1. The value 0 (No) converts to 0$. |
|
|
Hyperlink |
Not applicable. |
|
|
AutoNumber |
Short Text |
Not allowed if the AutoNumber field serves as a primary key. |
|
Long Text |
Not allowed if the AutoNumber field serves as primary key. |
|
|
Number |
Not allowed if the AutoNumber field serves as a primary key. |
|
|
Date and Time |
Date/Time: Not allowed if the AutoNumber field serves as a primary key. Date/Time Extended: Not allowed. |
|
|
Currency |
Not allowed if the AutoNumber field serves as a primary key. |
|
|
Yes/No |
Not allowed if the AutoNumber field serves as a primary key. |
|
|
Hyperlink |
Not applicable. |
|
|
Yes/No |
Short Text |
Original text must consist only of Yes, No, True, False, On, or Off. |
|
Long Text |
Original text must consist only of Yes, No, True, False, On, or Off. |
|
|
Number |
Zero or Null converts to No, all other values convert to Yes. |
|
|
Date and Time |
Date/Time: Null or 12:00:00 AM converts to No, all other values convert to Yes. Date/Time Extended: Not allowed. |
|
|
Currency |
Zeroes and Nulls convert to No, all other values to Yes. |
|
|
AutoNumber |
Access coverts all values to Yes. |
|
|
Hyperlink |
Not applicable. |
|
|
Hyperlink |
Short Text |
If the original text contains a valid Web address, such as adatum.com , www.adatum.com, or http://www.adatum.com, Access converts the text to a hyperlink. Access tries to convert other values, meaning that you see underlined text, and the mouse cursor changes when you point at the link, but the links do not work. The text can contain any valid Web protocol: http://, gopher://, telnet://, ftp://, wais://, and so on. |
|
Long Text |
See the previous entry. The same restrictions apply. |
|
|
Number |
Not allowed when a Number field is part of a relationship. If the original value is in the form of a valid I.P. address (four number triplets separated by a period: nnn.nnn.nnn.nnn) and the numbers happen to coincide with a Web address, the conversion results in a valid link. Otherwise, Access appends http:// to the beginning of each value, and the resulting links are not valid. |
|
|
Date/Time |
Access appends http:// to the beginning of each address, but the resulting links will almost never work. |
|
|
Currency |
Access appends http:// to the beginning of each value, but like dates, the resulting links will almost never work. |
|
|
AutoNumber |
Not allowed when the AutoNumber field is part of a relationship. Access appends http:// to the beginning of each value, but the resulting links will almost never work. |
|
|
Yes/No |
Access converts all Yes values to -1 and all No values to 0, and appends http:// to the beginning of each value. The resulting links do not work. |
