Data type mismatch in criteria statement

[wvmbark]

I get a data type mismatch in the criteria statement error when the INSERT runs. The DELETE seems to working fine. All fields are text.

If someone could straighten me out, I'd really appreciate it.

[HTML]Private Sub blnSelect_AfterUpdate()

Dim strSql As String
Dim txtMR_ExID As String
Dim txtEx_180_ID As String
strMR_ExID = Me.Parent.txtMR_ExID
strEx180_ID = Me.txtEx180_ID

DoCmd.SetWarnings (True)
If Me.blnSelected Then
strSql = "INSERT INTO tblMR_ExHist ( [MR_ExID], [Ex180_ID] )" & _
"SELECT tblMR_Ex.[MR_ExID], tblEx180.[Ex180_ID] " & _
"FROM tblMR_Ex INNER JOIN tblEx180 ON (tblMR_Ex.Tail = tblEx180.Tail) AND (tblMR_Ex.ATA = tblEx180.ATA)" & _
"WHERE tblMR_Ex.[MR_ExID]= " & strMR_ExID & " AND " & _
"tblEx180.[Ex180_ID]= " & strEx180_ID
Else
strSql = "DELETE tblMR_ExHist.[MR_ExID], tblMR_ExHist.[Ex180_ID]" & _
"FROM tblMR_ExHist " & _
"WHERE tblMR_ExHist.[MR_ExID]= " & strMR_ExID & " AND " & _
"tblMR_ExHist.[Ex180_ID]= " & strEx180_ID
End If
DoCmd.RunSQL (strSql)
Debug.Print strSql
DoCmd.SetWarnings (True)

End Sub
[/HTML]

Thanks for any help!!!

Mike

[Denburt]

Well according to the following statement you made "All fields are text." I would be led to believe that your variable strEx180_ID is a string and the field .[Ex180_ID] is also a string? If so then the SQL should look more like the following:

strSql = "DELETE tblMR_ExHist.[MR_ExID], tblMR_ExHist.[Ex180_ID]" & _
"FROM tblMR_ExHist " & _
"WHERE tblMR_ExHist.[MR_ExID]= " & strMR_ExID & " AND " & _
"tblMR_ExHist.[Ex180_ID]= '" & strEx180_ID & "'"

Otherwise if your field [Ex180_ID] is a number then the SQL statement looks OK just check and make sure that the variable is declared with the same type of number i.e. long, integer etc.