VBA 中 SQL 查询的数据类型不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22788173/
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
Data type mismatch on SQL Query in VBA
提问by 1337Atreyu
I am trying to do an SQL query in VBA to retun a specific case number. Whenever I execute the query, it returns an error of "Data Type Mismatch in Criteria Expression". I am passing the query an integer to use to query an autonumber primary key.
我正在尝试在 VBA 中执行 SQL 查询以重新调整特定的案例编号。每当我执行查询时，它都会返回“条件表达式中的数据类型不匹配”错误。我正在向查询传递一个整数以用于查询自动编号主键。
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Dim strSQL As String, strManager As String
Set c = New ADODB.Connection
c.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Commit Tracker.accdb; Persist Security Info=False;"
strSQL = "SELECT * FROM CommitTrk WHERE CASE_ID_NBR = '" & CInt(frmCommitViewer.lstCases.Value) & "'"
Set r = c.Execute(strSQL)
Of course the debug hilights the execute command. Any help would be appreciated. Am I passing the wrong datatype to match the autonumber? If so, what datatype should I be using? Thanks!
当然，调试会突出执行命令。任何帮助，将不胜感激。我是否传递了错误的数据类型来匹配自动编号？如果是这样，我应该使用什么数据类型？谢谢！
回答by Dmitry Pavliv
if CASE_ID_NBRhas numeric type, you should use it without quotes:
如果CASE_ID_NBR有数字类型，则应不带引号使用它：
strSQL = "SELECT * FROM CommitTrk WHERE CASE_ID_NBR = " & CInt(frmCommitViewer.lstCases.Value)
you may also want to read this: Global Variables in SQL statement
您可能还想阅读：SQL 语句中的全局变量
