.At some point, you may need to change data types for fields that already contain data. Perhaps you imported data, and Microsoft Access didn't set the data types as you intended. Or maybe the data type you set for a field is no longer appropriate.
Before converting from one data type to another, consider how the change will affect your entire database. Which queries, forms, and reports use the field that is being converted? You may need to change expressions that depend on the changed field.
The most common data type changes fall into the following four categories: converting to Text from other data types; converting from Text to Number, Currency, Date/Time, or Yes/No; converting between Currency and Number or between Text and Memo; and changing the FieldSize property setting for Number fields.
This topic provides reference information about:
Converting to Text from other data types
Converting from Text to Number, Currency, Date/Time, or Yes/No
Converting between Currency and Number or between Text and Memo
Changing the FieldSize property setting for Number fields
While you should store numbers that will be used in calculations in a Number or Currency field, you may find that a field that was originally just numbers now requires the use of letters and other non-numeric characters. For example, suppose you have a Number field that stores code numbers. If you later find you need to include letters, hyphens, parentheses, or other non-numeric characters as part of the code, you'll need to change the field's data type to Text.
Microsoft Access converts number values to text using a General Number format, and date values to text using a General Date format. The converted values will not include any currency symbols or other special formatting characters you set for the field.
Return to top
If you have data stored in a Text field and want to change the field to another data type, Microsoft Access can convert values that are appropriate for the new data type. For example, if you have numbers stored in a Text field and you want to do mathematical calculations on your data, you must convert the field to the Number or Currency data type. As long as all the data stored in the field consists of only numbers, you can change the data type without losing data.
For Text to Number data type conversions, decimal points and thousands separators are interpreted appropriately. Currency symbols are interpreted according to the regional settings specified by double-clicking Regional Settings in Windows Control Panel.
For Text to Date/Time conversions, most date and time formats are converted correctly. Date and time formats are interpreted according to the regional settings specified by double-clicking Regional Settings in Windows Control Panel.
For Text to Yes/No conversions, the words Yes, True, or On are converted to a Yes value, and No, False, or Off to a No value. To display Yes or No, you must click the Lookup tab in table Design view and set the DisplayControl property to Text Box. (You can also convert from Number to Yes/No data type: zero or Null values convert to No and nonzero values to Yes.)
Return to top
You can always convert between the Currency and Number data types. You should always store monetary values using the Currency data type to ensure that calculations are correct. You should also use a Currency data type if you plan to perform many calculations on a field that contains numbers with one to four decimal places. Currency fields use a fixed-point calculation method that avoids rounding errors. You can always convert between the Text and Memo data types. For example, if you want to store longer text values than you originally intended, you can change a field to the Memo data type. However, if a field doesn't have to store more than 255 characters, you should use the Text data type.
Return to top
For fields with the Number data type, the FieldSize property setting determines the specific number type: Byte, Integer, Long Integer, Single, Double, Decimal, or Replication ID (GUID). If you convert a field to the Number type, consider whether you need to change the FieldSize setting for the values you store in the field. If you change the FieldSize setting from a larger size, such as Double, to a smaller value, such as Integer, you must make sure that the values stored in the field fit in the new field size. If you change to a FieldSize setting that doesn't allow the number of decimal places included in your current values, the numbers are rounded. For example, if you change a field from Double to Long Integer, decimal numbers are rounded to the nearest whole number. Additionally, if the values are too large to be stored in the new size, they will be deleted and replaced with Null values.
To view a summary of data type conversion results, click .
For information on the range of values each field size can store, click .
Return to top