Dates in Access 2k changed during DoCmd.TransferSpreadsheet

[SQLBI]

Hi,

I have a DB which is poulated using DoCmd.TransferSpreadsheet. The problem i have is that for any dates prior to the 13th if any month, the date entered into the DB is converted from dd/mm/yyyy to mm/dd/yyyy, e.g. 03/01/2004 (3rd Jan 2004) is changed to 01/03/2004 (1st Mar 2004).

This is extremely confusing as the date values imported into the DB are in their numeric format, 03/01/2004 imported as 37989

I've checked the regional settings of the PC's in question and the data settings of the affected table and all is ok.

Has anyone encountered/fixed this problem before?



Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[WildHare]

I've checked the regional settings of the PC's in question and the data settings of the affected table and all is ok.

Well, not quite, as you've found.. the problem here is the difference between an expected INPUT data format, and an desired OUTPUT format. The regional settings guys affect only the latter.

In order to be consistent, some standard for the INCOMING data had to be arrived at. To the eternal chagrin of our European and non-American friends, they (MS) chose the standard Us American Yankee-Doodle Dandy format, e.g.

MM/DD/YYYY, in which 10/05/04 is October 5th, NOT May 10th.

I believe this sounds like it is the root of your problem, since only those days greater than the 13th of the month got converted (since they were, ahem, larger than 12)

I admit I often get confused at this point in the symposium, so before I say any more, I'll wait and see if this makes any sense to you...

Jim



Don't be sexist - Broads hate that.
Another free Access forum:

http://www.forumco.com/jmhare

More Access help stuff at

http://www.jmhare.com/wildhare.htm