Need to Convert "Text" Field to "Date" Field

[dukhunter99]

I am very new at Access and have a BIG problem. We use a access database to manage ID cards. The old company (we fired) created a access database with dates as actual "text" instead of "date" fields. Our new program uses access database but does not except the text string for a date. I need to convert the date as a text string to a date field. When I change the field in the table from text to date, it deletes all the entries. That is very bad as there are over 2,000 entries.

The format of the current text date string is 6 characters and displays as this: 020612

02 = Month
06 = Day
12 = Year

I need to convert this text to a date field with proper format of 02/02/2012. How do I do this in access without having to manually change every date before changing the field from "text" to "date" and erasing all current entries. I am very new at this and would really appreciate any help!

 

[pbaldy]

You can convert it with the DateSerial() function, along with the Mid() function to pull out the appropriate portion of the string. Look in help for more info on both. You can use that in an update query to change the value of that field or a newly created date field (it could also be used in any number of other places).

 

[bob fitz]

Paul
Could this be done with an update query? Something like:

UPDATE Table1 SET Table1.TextDate = Mid([TextDate],1,2) & "/" & Mid([TextDate],3,2) & "/" & Mid([textDate],5,2);

 

[pbaldy]

I suspect so Bob, though it would still be text. At that point changing the data type might work though.

 

[bob fitz]

I've tried using the update query and then changing the data type to date. It appears to work.