Type mismatch error caused by where clause, Any known workarounds?

Question

Not sure why but I am getting type mismatch error when I execute this part of my Access form. I checked the data types of all fields involved in SQL server, they are all matching. The InspectID and incdntno are both type 'INT' in both tables and the inspdate and Dt_inspect are 'datetime'.

I found that the mismatch is likely caused by the where clause of my query because the incdntno gets Dim'd as string (in SQL it's INT) I tried changing the datatype of Incdntno to varchar in both tables, thinking that that would survive the Dim to string and not cause a mismatch, I'm unsure if that worked because when I ran it next it threw an ODBC error.

Private Sub Text79_AfterUpdate()
  Dim conn As ADODB.Connection
  Dim sSQL As String

  Set conn = CurrentProject.Connection

  If IsNull([Incdntno]) Then
    Me.Dirty = False
  End If
  Dim InspNo As String
  InspNo = Incdntno.Value
  sSQL = "INSERT INTO tblFieldIncident_Complaint_InspHist ( Incdntno, InspectID, Dt_Inspect ) SELECT Incdntno, InspectID, InspDate FROM tblInspect WHERE Incdntno='" & InspNo & "';"
  conn.Execute sSQL
  Me.Refresh


End Sub

Gordon Linoff Gordon Linoff · Accepted Answer · 2014-02-28T15:52:22

I would suggest leaving out the single quotes:

sSQL = "INSERT INTO tblFieldIncident_Complaint_InspHist( Incdntno, InspectID, Dt_Inspect )
          SELECT Incdntno, InspectID, InspDate
          FROM tblInspect
          WHERE Incdntno=" & InspNo & ";"
-------------------------^-------------^

Type mismatch error caused by where clause, Any known workarounds?

1 Answers