How to convert text field (i.e. 09.14.09) to a date format?

[execution]

In my database i have fields with dates in this format ( 09.09.09). the field is set to text because I imported the data from excel sheets. I really need to convert these values to a proper date format since I have to sort my entries by date.

Any help would be much appreciated.
Thanks

 

[dbDamo]

Open your table

Highlight the column with incorrectly formatted dates

Go to Edit > Replace

Put a . in the Find What field

Put a / in the Replace With field

Select Any Part of Field from the list in the Match field

Press the Replace All button

Open your table in design view and change the Data Type to Date/Time

 

[namliam]

Depends on your format, if MM/DD/YY or YY/MM/DD then yes you can do as dbDamo suggests, though you will probably not want to do it in table design rather using the Datevalue function.

If your format is other than above (ie. DD/MM/YY) then using datevalue is a bad idea and using DateSerial in conjunction with left, right and mid functions is the way to go.

Hope you can get started with this information, ask more detailed questions if you run into problems.