About changing a field's data type (MDB)

 Note   The information in this topic applies only to a Microsoft Access database (.mdb).

At some point, you may need to change data types (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) 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 (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) 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.

ShowConverting to Text from other data types

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 by using a General Number format, and date values to text by using a General Date format. The converted values will not include any currency symbols or other special formatting characters you set for the field.

ShowConverting from Text to Number, Currency, Date/Time, or Yes/No

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 in Microsoft 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 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 the words "Yes" or" No" instead of checkboxes, you must click the Lookup tab in table Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.) and change the DisplayControl property from Check Box to Text Box. (You can also convert from Number to Yes/No data type: zero or Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.) values convert to No and nonzero values to Yes.)

ShowConverting between Currency and Number or between Text and Memo

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 a Text field to a Memo field. 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.

ShowChanging the field size of Number fields

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 size, 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. Also, when you attempt to save a table containing values that are too large to be stored in the new size, Microsoft Access warns you that they will be deleted and replaced with Null values if you proceed.

ShowField data type conversion results

The following is a list of the results of common data type conversions when the table contains data.

From All data types to AutoNumber

Prohibited by Microsoft Access.

From Text to Number, Currency, Date/Time, or Yes/No

Converts text to appropriate values. Make sure values fit in the new data type; inappropriate values are deleted.

From Memo to Text

Simple conversion. Data longer than the FieldSize setting is truncated.

From Number to Text

Converts values to text. Numbers adopt General Number format.

From Number to Currency

Converts numbers to currency. Make sure values fit in the new data type; inappropriate values are deleted.

From Date/Time to Text

Converts values to text. Dates or times adopt General Date format.

From Currency to Text

Converts values to text. Text doesn't include currency symbols, such as $.

From Currency to Number

Simple conversion. Make sure values fit in the new data type; inappropriate values are deleted.

From AutoNumber to Text

Converts values to text. Values may be truncated depending on FieldSize setting.

From AutoNumber to Number

Simple conversion. Make sure values fit in the new data type; inappropriate values are deleted.

From Yes/No to Text

Converts values to text.

 
 
Applies to:
Access 2003