field has date typed as text and i need to convert to date

[tagteam]

I have a field that the date is typed out as

Saturday 16 February 2013 11:54:06 PM

I need to be able to convert that to a real date. Is this something better done in excel or can this be done it access.

The date is typed out, it is not formatted to look like a date field. The data came downloaded from an ecommerce site with the date actually typed out for some reason.

[orange]

You could try CDate()

[IrogSinta]

This query breaks the string into their time, date, and date/time components:

SELECT CDate(Mid([richtext],InStrRev([richtext]," ")+1)) AS Time1, CDate(Mid([richtext],InStr([richtext]," ")+1,InStrRev([richtext]," ")-InStr([richtext]," ")-1)) AS Date1, [Date1]+[Time1] AS MyDate
FROM Table1;

Or you can use an update query to update a Date data type field in your table:

UPDATE NameOfTable SET NameOfTable.NameOfDateField = CDate(Mid([DateString],InStr([DateString]," ")+1,InStrRev([DateString]," ")-InStr([DateString]," ")-1))+ CDate(Mid([DateString],InStrRev([DateString]," ")+1));

Ron

[nick404]

Is this in a form? Report?
In the properties you can set the Data control source to =Date () and it will always automatically insert the current date. Or in Format properties set the format to whichever style of date you desire (general date, long date, short date, medium date) and click 'for dates' in the "show date picker" property and you can pick the date you want if you are entering data into a form.