VBA中SQL查询上的数据类型不匹配 [英] Data type mismatch on SQL Query in VBA

问题描述

我正在尝试在VBA中执行SQL查询以重新调整特定的案例编号.每当执行查询时，它都会返回错误条件表达式中的数据类型不匹配".我正在向查询传递一个整数，该整数用于查询自动编号的主键.

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.

    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!

推荐答案

如果 CASE_ID_NBR 具有数字类型，则应使用不带引号的数字

if CASE_ID_NBR has numeric type, you should use it without quotes:

strSQL = "SELECT * FROM CommitTrk WHERE CASE_ID_NBR = " & CInt(frmCommitViewer.lstCases.Value)

您可能还需要阅读以下内容: SQL语句中的全局变量

you may also want to read this: Global Variables in SQL statement

这篇关于VBA中SQL查询上的数据类型不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！