Converting text date to actual date

[aziz rasul]

I have to regularly import data from Excel which has a column with dates like 1st October 2012. How can I convert this to a proper date in code. If I use CDate, it gives me a mismatch error.

 

[aziz rasul]

Augustus has a lot to answer for.

I guess I could replace the months with there first 3 characters and then make the changes you suggested.

 

[JohnLee]

Hi,

I've just did a little bit of testing, I'm using Access 2000 but I've just imported an excel file that has dates in the format you specified for example:

21 August 2012
02 September 2012
11 October 2012

when I imported the excel file as a new table I then went into the design view and changed the Data Type from Text to Date/Time format, saved the table and then open up the table and the above dates displayed as:

21/08/2012
02/09/2012
11/10/2012

I know it's a manual process, but no coding required.

Alternatively if your excel file retains it's file name each time it is updated you could link to that excel file and then have a table within your database that you append this data to, and on appending to your table that has the date field already of Date/Time Data Type, your dates will automatically on being appending take the desired format.

Clearly these may not be considered to be the best method of achieving what you want and the experts may come up with something you may consider more suitable.

Just something to consider using untill you can come up with a better solution.

Regards

John

 

[aziz rasul]

The filenames will be different and the dates I get are

1st October 2012
3rd February 2012
etc.

which if appended to a date\time field I think will not work.

 

[JohnLee]

Hi,

No it won't work if the date is written as you have stated, perhaps you can encourage the senders to write the dates in your desired format, I know that wouldn't happen overnight, but if you don't ask you don't get.

Sorry I couldn't be of more help.

Regards

John

 

[aziz rasul]

Well if the worst comes to worse and I don't get what I want, I can simply create 12 update queries to change all the full month names to their equivalent short month names and then create 3 update queries to replace any occurence of 'st', 'rd' and 'th'. By executing the SQL strings in code I can automate the whole process as part of my import routine.