Changing TEXT field to DATE field.

[premanands]

I have a big database with 5000+records. The user saved the dates as text in this format for example, "05/95" (may/1995).
I am trying to convert this text field into Date field.
When I change the datatype from "text" to "short date" ...MS Access automatically converts the text entries into date format.

For example, 05/95 to 05/01/1995.. this works fine..

But when the year is 2000+ it goes wrong.

For example, 05/02 is converted to 05/02/2003.

MS Access assumes 02 as month rather than a year.
Is there a way to work around this?
Solution is needed soon,
Thanks,
prem

 

[cpod]

You could just add a new date field to your table and then, in a query, use something like this to update the new field:

DateValue(Left([textdatefieldname],3) & "01/" & Right([textdatefieldname],2))

 

[premanands]

This sounds like a good work around. Thanks for the fix.!