Data type mismatch on SQL Query in VBA

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!

Answer

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)

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

Advertisement

Answer