Interpreting Dates

[kmcculler]

Hi,
My problem lies particualry int the way Access interprets dates when read in from a file. Ive tried having several diffrent date formats in the file but I can't seem to find one that access consistanlty interprets correctly. (this is a flat file with a series of sql statments to be run on the db)
My first problem was with dates like "05/12/02" what is the day,month, and year? Access will read them in according to your regional settings ie if your windows date is set YYYY/MM/DD it reads December 2, 2005 if set to MM/DD/YY it reads as May 12, 2002 and so on. My best luck was with the files date format set to May/12/2002. Clearly this difines the day month and year... Access was happy until it went international..
We have users in the US and in the Netherlands and thier settings are in Dutch. This doesn't work with them because some of the months have diffrent abbreviations on thier system ie March is Maa not Mar, October is Okt not Oct, ect. So access ignores the entire date and throws it out.
I can't seem to think of any format that would work consistantly. I even tried to use Julian dates, but I couldn't get access to accept them at all. Does anyone out there have any more ideas for me??? I need some help. Kris McCuller
Programmer Portiva Corp.
kmcculler@portiva.com

 

[raskew]

If we can assume that all dates in a particular file have the same format (whatever it may be), and that imported date fields are in fact in text format, then:

Build a user defined function that:
1. Manipulates the text field into US short-date format.
2. Use the DateValue() function to convert the results of Step 1 to an Access-acceptable date field.