By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

Data type mismatch in criteria statement

P: 5
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
Mar 15 '07 #1
Share this Question
Share on Google+
1 Reply


Denburt
Expert 100+
P: 1,356
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.
Mar 15 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.