Changing format of data

[PatrickX]

We're trying to update the database, change some fields
from *text* to *date/time*, add a few fields, remove some.

The bulk of the data will stay the same. When I tried to
just change the database, it lost all the old data (I was
working with a copy).

I didn't design the origanal database, but now I've got to
fix it. It's pretty big, I don't know if manual work is
really an option.

Thank you!

 

[John Nurick]

Hi Patrick,

Keep working with a copy for safety's sake. If simply changing the field
type doesn't give the results you need, do it this way:

1) add a new field of the desired type (e.g. Date/Time)
2) create an update query containing an expression that converts the
values in the text field into date/time values and updates the new
field.
3) after doing it successfully, delete the now unwanted text field.

The expression in the update query naturally will depend on how the date
appears in the text field. Post back with more detail if you need more.