Convert Text to Date

[jfredel via AccessMonster.com]

I have a text field with date like data in it but its text data. THe field
[End_date] looks like 31Dec2009 - how can i reformat that so its a recognized
date field that I can do calculations on? Ulitmately, I'd like the field to
be 12/31/2009. Assistance is greatly appreciated. Not a VB expert so doing
it in a query would work better for me.

Thanks in advance!!

Jeff

 

[fredg]

I have a text field with date like data in it but its text data. THe field
[End_date] looks like 31Dec2009 - how can i reformat that so its a recognized
date field that I can do calculations on? Ulitmately, I'd like the field to
be 12/31/2009. Assistance is greatly appreciated. Not a VB expert so doing
it in a query would work better for me.

Thanks in advance!!

Jeff

If all the dates are in that exact same format "ddmmmyyyy" (no spaces)
you can use an Update query to convert that text value to a date
value.

Back up your table first.

Add a new field to your table.
Name it ADate
Set it's datatype to Date/Time.

Then create an Update query:
Update YourTable Set YourTable.[ADate] = CDate(Format([TextDate],"@@
@@@ @@@@"))

Afterwards, assuming all is well, you can delete the existing
[TextDate] Field.

The actual formatting of the date is a different matter than it's
stored value.
The date will appear formatted in whatever the computer date format
setting is or in whatever format is selected for that field in it's
properties.