sql - data type mismatch error with OpenRecordSet

Question

Using Access 2007. I'm trying to write a VBA function that will construct a query from the table name, fields, and values that I pass as parameters. I keep getting a "Run-time error '3464': Data type mismatch in criteria expression."

Here's the code:

Function getPrimaryFromForeign(db As Database, table As String, field As String, _
                            value As Long, _
                            field2 As String, value2 As Long) As Long

Dim sStr As String
Dim istr As String
Dim rs As Recordset



sStr = "select * from " & table & " where " _
        & field & "='" & value & "' and " & field2 & "='" & value2 & "'"
    istr = "insert into " & table & "(" & _
    field & "," & field2 & ") values ('" & value & "','" & value2 & "')"



Set rs = db.OpenRecordset(sStr)

If rs.RecordCount < 1 Then
    db.Execute (istr), dbFailOnError
    Set rs = db.OpenRecordset(sStr)
End If
getPrimaryFromForeign = rs("id")

End Function

The error occurs at the line: Set rs = db.OpenRecordset(sStr)

I think it has something to do with the variable types of Value and Value2. But I've checked them using typename(), and they're both Long when OpenRecordSet() is called. The query is on a table where both of those fields are of type Number. So why is there a type mismatch?

rsbarro rsbarro · Accepted Answer · 2011-04-20T01:53:42

I haven't used Access in a long time, but I think that the problem is because you're enclosing Value and Value2 in single quotes in your SQL statement, and not enclosing the fields in the WHERE clause in brackets.

Try it like this:

sStr = "select * from " & table & " where [" _
            & field & "] = " & value & " and [" & field2 & "] = " & value2
istr = "insert into " & table & "([" & _
            field & "], [" & field2 & "]) values (" & value & "," & value2 & ")"

sql - data type mismatch error with OpenRecordSet

2 Answers