Date inserted into Access Database from Excel using VBA changes to US date

0
votes

So I'm using a VBA generated query to insert a bunch of records from an Excel spreadsheet into an Access Database.

My query basically looks like this:

accName = r.Offset(x, 0)
AccNum = r.Offset(x, 1)
sector = r.Offset(x, 2)
holding = r.Offset(x, 3)
holdingvalue = r.Offset(x, 4)
holdingdate = CDate(r.Offset(x, 5))

sSQL = "INSERT INTO Holdings (AccName, AccNum, Sector, Holding, HoldingValue, HoldingDate)" & _
" VALUES ('" & Replace(accName, "'", "''") & "', '" & AccNum & "', '" & sector & "', '" & Replace(holding, "'", "''") & "', '" & holdingvalue & "', #" & holdingdate & "#)"

An example of the generated query looks like this:

INSERT INTO Holdings (AccName, AccNum, Sector, Holding, HoldingValue, HoldingDate) 
VALUES ('Account 123', '472700', '', 'IShares S&P 500', '54379.15', #03/12/2012#)

The wrongly-functioning bit is that the date in the query has gone from a UK date (dd/mm/yyyy) to a US date (mm/dd/yyyy). I have tried also formatting the date on the spreadsheet as an ISO date (yyyy-mm-dd) but is always goes as a US date, even when I don't use cdate. Any idea what's going on here?

Edit: When I step through the code and hover on the holdingdate variable it says "03/12/2012" and the query is the same as above (ie with the date in dd/mm/yyy format, despite me defining holdingdate as holdingdate = Format(r.Offset(x, 5), "yyyy-mm-dd"). The dates in the cells are also serials (i.e. 41246 when displayed as a number). When I look in the access database after the data has been input the date is shown as 12/03/2012. I just don't understand....

1
sqlexcelvbadatems-access
How about CDate(Format(r.Offset(x,5), "dd/mm/yyyy") Might have something to do with your excel settings too. - Bmo
Is your system date/time set to UK? You may have set your excel Date format to UK but left your system format as US. - Zaider
Yes, both my excel and system time is set to UK. No idea why it's doing this. Tried Bmo's method above to no avail - harryg
Try "dd mmm yyyy" this should return '01 Jan 2012', then it doesn't matter what your settings are. - twoleggedhorse
Even with holdingdate = Format(CDate(r.Offset(x, 5).Value), "dd mmm yyyy") holdingdate is still returned as 03/12/2012. The format function seems to do nothing - harryg

1 Answers

0
votes

Solved! I had previously dim'd holdingdate as a date so whatever format I gave it, it was just returning an excel date. Changing it to Dim holdingdate as string has solved the problem and the date is now passed as a string that Access can happily eat with worrying about US/UK dates.