Access does not recognise my date format correctly and shows error

[Bruce]

Access does not recognise my date format correctly and shows errors

Hi,

Access does not recognise my systems date format correctly in a link table
to a .csv file.

BACKGROUND
I am using MSAccess 2K3, SP3
I am in Australia which has dd/mm/yy format and I understand internally
MSAccess still uses the US mm/dd/yy format.

Here is what am I trying to do.
1) Download a html table to a .csv from a US server.
2) The .csv is attached to the database via a link table.
3) Import the data in my link table to Access.

I have automated step 1 and step 3, no problems but the errors are in the
date format conversion.
I have observed that after running step 1), that if I manually open the file
and save it in Excel it updates the .csv with my systems date format of
dd/mm/yy. I can then run step 3 and it imports correctly.


As a work around I figure I should automate the open and save of the .csv
file from Access with the procedure below but it does not seem to change the
format to my locale date format settings. I think its using a MSAcess method
of Save rather than the Excel one.

Public Sub upd_CSV()
Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
arrXL = Array("YahooDownload.csv")
For Each b In arrXL
Set xls = CreateObject("Excel.Application")
xls.Visible = False
For Each a In arrXL
Set xwkb = xls.Workbooks.Open(CurrentProject.path &
"\Download\" & a, UpdateLinks:=2)
xwkb.Save
xwkb.Close True
Next a
Set xwkb = Nothing
xls.Quit
Set xls = Nothing
Next b
End Sub

I’ve been trying to crack this one for months. Does anyone have any
suggestions?

Bruce

 

[Allen Browne]

My experience (also in Australia, using Access for 15 years), is that the
best approach is to import the text file into a table that has a TEXT type
field for the date column. Then use a query to turn that into a real date
and populate the real date field. CVDate() works, or if necessary you can
parse it with Left(), Mid() and Right() and use DateSerial() to get the real
date.

There are 3 other cases (other than reading text files) where Access can get
our Aussie dates wrong. Here's how to avoid those issues:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html