Date Import Problem to a table.

[Centaur]

I am in a country that does not use the standard American system of date
mm/dd/yy; the dating I use is dd/mm/yy.

When importing dates from a CSV file is as follows - If a date in mm/dd/yy
format is 06/26/04 then it will get converted to dd/mm/yy (26/06/04) . NB
the CSV file imports dates as text with a quote on each side eg "06/15/2004
23:53" after importing I push it through a query to remove the quotes using
Replace([Time],Chr(34),"")

The problem happens if the Day number is less than 13, i e 10th June 2004 in
mm/dd/yy format is 06/10/04 it will remain 06/10/04 when it is converted to
dd/mm/yy and obviously the wrong date being 6th October 2004.

Any ideas, help would be appreciated please.

Centaur

 

[Allen Browne]

Safest approach would be to import the text file into a table as a Text
field.
Then use a calculated field in a Update query to parse the date, and
populate the real date field:

DateSerial(Mid("06/15/2004", 7,4), Left("06/15/2004",2),
Mid("06/15/2004",4,2))


We have found 3 cases where Access gets confused about the date format. See:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html