Changnig text to date

[rowk]

Hi there. I've got a month ID field with is YYYY"M"MM eg June 05 is 2005M06> When I import this into access it's a text field - how can I convert this to a date format. I know I can do it in excel - but can't seem to work it out.

Any help would be appreciated.

Row

 

[Andrew Fergus]

Hi Row
Create a new field in the import table (into which you can store the converted date, format it as date), create a new query (an Update Query) based on the imported table, add the new field to the query, and in the 'update to' part of the new field enter this :

DateSerial(Left([date],4),Right([date],2),1)

Please note I based this on a field name called 'date', be sure to use your actual field name (month id per your post?). Save and run the query. You should now have a field in the import table that has the date in the correct format.
HTH, Andrew

 

[rowk]

Thanks for that - you're a legend!!