What I mean is when I import this file from excel i have
got all the datafields in Text format and need some of
them in Date format. Is it possible to write some code to
change the text field to a short date field when it has
been loaded.
The main problem is not so much any restrictions in the Date type, but the
kind of garbage that people can type into text fields pretending that they
are real data. I would recommend:-
1: back up the database
2: insert a new field, formatted as a DateTime,
3: use an update query to populate the new column
UPDATE MyTable
SET MyNewDateField = CDATE(MyOldTextField)
WHERE MyOldTextField IS NOT NULL;
4: back up the database
5: manually check the values to make sure they have been translated
correctly and update any errors. If it's awful, go back to (3) and try
another conversion method, like DATESERIAL(LEFT(MyOldTextField,4)...) and
so on, using an earlier backup.
6: back up the database
7: if you are really happy, delete the old text field and (yes) back up
everything again. Twice. Oh, and do a compact to recover the space.
8: If you want, you can rename the new field to what the old one used to
be, which will help some of your queries to survive the change. You may
still have some problems with fields or queries or reports or code that is
expecting the old field type and gets a new one. If necessary, you can
always return to a back up file. You did do a back up, didn't you?
9: once it is all tested and proved, release the new db to your users.
Hope that helps
Tim F
