By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,894 Members | 1,460 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,894 IT Pros & Developers. It's quick & easy.

Access 2003, unable to change date format from excel

P: 14
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.
Nov 23 '07 #1
Share this Question
Share on Google+
3 Replies

Expert 2.5K+
P: 3,532
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:

Expand|Select|Wrap|Line Numbers
  1. NewDate = Mid(OriginalDate,5,2) & "/" & Right(OriginalDate,2) & "/" & Left(OriginalDate,4)
Welcome to TheScripts!

Linq ;0)>
Nov 23 '07 #2

P: 14
Thank you missinglinq, it worked…I was stumped…you a genius.
Nov 23 '07 #3

Expert 2.5K+
P: 3,532
Glad we could help!

Linq ;0)>
Nov 23 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.