About changing a field's data type (MDB)

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.

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 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 values convert to No and nonzero values to Yes.)

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.

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.

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.