使用 VBA 更改美国日期从 Excel 插入 Access 数据库的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13684457/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Date inserted into Access Database from Excel using VBA changes to US date
提问by harryg
So I'm using a VBA generated query to insert a bunch of records from an Excel spreadsheet into an Access Database.
因此，我使用 VBA 生成的查询将 Excel 电子表格中的一堆记录插入到 Access 数据库中。
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?
错误的功能是查询中的日期从英国日期 (dd/mm/yyyy) 变为美国日期 (mm/dd/yyyy)。我也尝试将电子表格上的日期格式化为 ISO 日期 (yyyy-mm-dd)，但始终作为美国日期，即使我不使用 cdate。知道这里发生了什么吗？
Edit: When I step through the code and hover on the holdingdatevariable 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....
编辑：当我逐步执行代码并将鼠标悬停在holdingdate变量上时，它会显示“03/12/2012”，并且查询与上面相同（即日期为 dd/mm/yyy 格式，尽管我将 holddate 定义为holdingdate = Format(r.Offset(x, 5), "yyyy-mm-dd")。单元格中的日期也是序列号（即显示为数字时为 41246）。当我在输入数据后查看访问数据库时，日期显示为 12/03/2012。我只是不明白...... .
采纳答案by harryg
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 stringhas solved the problem and the date is now passed as a string that Access can happily eat with worrying about US/UK dates.
解决了！我以前将持有日期作为日期，所以无论我给它什么格式，它都只是返回一个 excel 日期。将其更改为Dim holdingdate as string已经解决了问题，并且日期现在作为字符串传递，Access 可以愉快地食用，而不必担心美国/英国日期。
