|
|
|
In an earlier lesson we showed how to specify field properties at table definition time. You can also modify these field properties after the table has been defined. The need to change a field type may arise if, for example, you discover a numeric product ID field must be changed to text because new product ID's will include non-numeric characters.
If you are changing the data type of a field in a table that already contains data, Access will attempt to convert the data. For example, if the field is text and you are converting it to numeric, valid numeric text strings will be converted to numeric numbers. Access can't work miracles, though. If the field value can't be converted (i.e., "1-2," "3 4," "5a"), Access will erase the field value.
In the following animation, the Purchase Date field is first converted from text to date/time and then to numeric. It is a contrived example designed to demonstrate how field values can be converted (and how dates are represented).
|
|
|
In the above animation the values in the first two records survive the conversion even though they are not uniform. The value in the third record ("jan 3rd 1998") can't be converted from text to date/time, so it is erased.
Notice what happens when the field type is changed from date/time to numeric.
What's going on here? The answer is date/time values are stored as numeric numbers. (You can choose what format they are displayed as, but internally they are represented as numeric values.) This allows date/time values to be sorted, compared and used in equations.
 |
 |
|
Copyright 1997 by the Curators of the University of Missouri |
