Access/VBA 和 SQL WHERE 子句格式问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10016740/
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
Access/VBA and SQL WHERE clause format issue
提问by graidan
I have read a huge pile of problems and solutions, and I just can't figure out what I'm doing wrong.
我已经阅读了大量的问题和解决方案，但我无法弄清楚我做错了什么。
BounceDate = DateValue(txtBounceDate.Value)
bncSql = "DELETE _BounceMaster.* FROM _BounceMaster" & _
" WHERE _BounceMaster.DateCheck >= #" & BounceDate & "#;"
DoCmd.RunSQL bncSql
_BounceMaster.DateCheck is in Date/Time format, which I think may be the issue, but I can't figure out what different format it should be in, or how to get there. As best as I can tell, BounceDate is correct - even using CDate didn't make a idfference. I have gotten both data mismatch errors, and currently, with code as above, I am getting syntax errors. What am I doing wrong?
_BounceMaster.DateCheck 是日期/时间格式，我认为这可能是问题所在，但我不知道它应该是什么不同的格式，或者如何到达那里。尽我所知，BounceDate 是正确的 - 即使使用 CDate 也没有产生任何影响。我收到了两个数据不匹配错误，目前，使用上述代码，我收到了语法错误。我究竟做错了什么？
采纳答案by RedFilter
It should be
它应该是
DELETE FROM _BounceMaster
not
不是
DELETE _BounceMaster.* FROM _BounceMaster
You should be using parameterized queries, as your code is subject to SQL injection attack.
您应该使用参数化查询，因为您的代码容易受到 SQL 注入攻击。
回答by Radek
I suppose the problem comes from date formatting. The BounceDatevariable is DateTime type, so when you concatenate with string type variable, VBA automatically casts DateTime variable into String type using date format from your regional settings.
我想问题来自日期格式。该BounceDate变量是 DateTime 类型，因此当您与 string 类型变量连接时，VBA 会使用区域设置中的日期格式自动将 DateTime 变量转换为 String 类型。
As I correctly remember, SQL interpreter from MS Access feels comfortable only with mm/dd/yyyy date format, so please try this:
我记得没错，MS Access 的 SQL 解释器只对 mm/dd/yyyy 日期格式感觉很舒服，所以请试试这个：
BounceDate = DateValue(txtBounceDate.Value)
bncSql = "DELETE _BounceMaster.* FROM _BounceMaster" & _
" WHERE _BounceMaster.DateCheck >= #" & Format(BounceDate, "mm/dd/yyyy") & "#;"
DoCmd.RunSQL bncSql
