Date text to Date/time

  • Thread starter Thread starter Box666
  • Start date Start date
B

Box666

I have imported a file into Access2003. All fields have come across
except for the date.Whilst it shows visually as "25Jan1991" it is in
text format.

Can somebody advise me please how to convert it to a Date / Tme field.
 
Create a new field datatype DateTime and then run an update query from the
text field.
 
This is going to be a little difficult. Are all the records set up like
"25Jan1991" with exactly 9 characters in ddmmmyyyy format? Leading zeros if
the day is under 10? If so, you have a chance. Run something like the
following in a query after replacing the "25Jan1991" with the actual field
name enclosed in [ ] instead of double quotes.

AreDates: IsDate(Left("25Jan1991",2) & "/" & mid("25Jan1991",3,3) & "/" &
Right("25Jan1991",4))

In the criteria, put the word False without quotes.

If you get any records returned either the string can't be evaluated as a
valid date or has null values. Fix those that have bad dates and decide what
to do about the nulls like not trying to update them.

Then in an update query use something like the following:
CDate(Left("25Jan1991",2) & "/" & mid("25Jan1991",3,3) & "/" &
Right("25Jan1991",4))

Again make sure to insert the proper field name.

As you are changing a lot of data, it would be very prudent to make a backup
of the table if not entire database first.
 
Back
Top