由 where 子句引起的类型不匹配错误，任何已知的解决方法? [英] Type mismatch error caused by where clause, Any known workarounds?

问题描述

不知道为什么，但是当我执行 Access 表单的这一部分时出现类型不匹配错误.我查了一下SQL server涉及的所有字段的数据类型，都是匹配的.InspectID 和 incdntno 在两个表中都是INT"类型，inspdate 和 Dt_inspect 是日期时间".

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'.

我发现不匹配很可能是由我的查询的 where 子句引起的，因为 incdntno 被 Dim'd 作为字符串(在 SQL 中它是 INT)我尝试将两个表中 Incdntno 的数据类型更改为 varchar，认为这会保留 Dim 到字符串并且不会导致不匹配，我不确定这是否有效，因为当我接下来运行它时它抛出了 ODBC 错误.

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

推荐答案

我建议去掉单引号:

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

这篇关于由 where 子句引起的类型不匹配错误，任何已知的解决方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！