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 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