Data type mismatch on SQL Query in VBA

Question

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!

Community · Accepted Answer · 2017-05-23 10:26:02Z

5

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

edited May 23, 2017 at 10:26

CommunityBot

11 silver badge

answered Apr 1, 2014 at 14:04

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

1337Atreyu Over a year ago

Perfect, that worked. I am still trying to wrap my head around this SQL thing. The formatting is throwing me off. I will accept the answer when it allows me to. I will read that in a bit. Thanks!

Dmitry Pavliv Over a year ago

in general it's common mistake when to use "SELECT ... WHERE Field1 = 1" (numeric) "SELECT ... WHERE Field1 = #01/01/2012#" (dates) and "SELECT ... WHERE Field1 = 'sometext'" (text)

perfo Over a year ago

Thanks, I had the same problem and the solution was that easy... my search criteria used 'Jan-24' and the data in the table was date not a string... Thanks

Collectives™ on Stack Overflow

Data type mismatch on SQL Query in VBA

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related