从Excel使用VBA更改插入Access数据库的日期 [英] Date inserted into Access Database from Excel using VBA changes to US date

问题描述

所以我正在使用一个VBA生成的查询，将一堆Excel电子表格中的记录插入Access数据库。

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

我的查询基本如下所示： / p>

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#)

错误的功能是，查询从英国日期（dd / mm / yyyy）到美国日期（mm / dd / yyyy）。我已经尝试将电子表格上的日期格式化为ISO日期（yyyy-mm-dd），但始终作为美国日期，即使我不使用cdate。
任何想法这里发生了什么？

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?

编辑：当我通过代码并将鼠标悬停在 holddate 变量它说03/12/2012，查询与上述相同（即日期为dd / mm / yyy格式，尽管我将holddate定义为 holddate = Format（r。偏移量（x，5），yyyy-mm-dd）。单元格中的日期也是序列号（即当显示为数字时为41246）
当我查看数据输入后访问数据库日期显示为12/03/2012。我只是不明白....

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....

推荐答案

已解决！我以前曾经暗淡的抱着日期，所以我给它的任何格式，只是返回一个excel日期
将其更改为 Dim holdingdate as string 已经解决了这个问题，日期现在被传递为一个字符串，Access可以高兴地吃，担心美国/英国的日期。

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.

这篇关于从Excel使用VBA更改插入Access数据库的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！