Access 2003, unable to change date format from excel

[Bface]

Hi all,

Hope everyone had a good holiday. I am having a difficult time changing the date format of a field from Excel. I have never had this problem before. I link the excel spreadsheet to my DB, from the spreadsheet I have an append query that appends only certain fields from the spreadsheet to a table. One of the fields is called [Booked Date]. The date in this field are displayed as ‘yyyymmdd’ ex:20070525

To change date format I usually create an expression in the query, like Format ([Booked Date], “mm/dd/yyyy”). That usually works perfectly but not this time, I get #error. I tried changing the field type in the table the records are being append to but the field does not get appended if I do. It only appends if the field type is set to text.
I have tried changing the cell format in excel to the Date format, but that did not work. I even tried changing it to text. When ever I change the format all the dates appear as 12/30/1899.

I don’t know why I can't change the date. I am assuming it probably has to do with how the file was created. Any tips any one can give, I would greatly appreciate.

[missinglinq]

To change date format I usually create an expression in the query, like Format ([Booked Date], “mm/dd/yyyy”).

The problem, I think, is that you have to start out with a valid date, and 20070525 is not a valid date! Your formatting will work fine if you start with 2007/05/25, changing it to 05/25/2007.

The 12/30/1899 is the date that Access defaults to when a non-date value is shoved into a field defined as a date!

Even CDate() won't help here. I think you need to set up a parsing routine to change the value into a string that Access will accept as a date; it actually will accept a string as a date if it looks like a date!

Where OriginalDate is what you start with and NewDate is your deisred date:

NewDate = Mid(OriginalDate,5,2) & "/" & Right(OriginalDate,2) & "/" & Left(OriginalDate,4)

Welcome to TheScripts!

Linq ;0)>

[Bface]

Thank you missinglinq, it worked…I was stumped…you a genius.

[missinglinq]

Glad we could help!

Linq ;0)>