SQL查询中的MS Access VBA数据类型不匹配错误

发布时间：2020-12-12 06:36:08 所属栏目：MsSql教程来源：网络整理

导读：我目前有以下MS Access SQL查询,它是Access VBA功能的一部分.它是在 previous question的帮助下构建的,您可以查看它以更好地了解它的工作原理. sqlJoinQuery = "SELECT tbl_grp_by.[Field1],tbl_grp_by.[Field2]," _ "Switch( " _ "Nz(tbl_grp_by.[maxfield3]

我目前有以下MS Access SQL查询,它是Access VBA功能的一部分.它是在 previous question的帮助下构建的,您可以查看它以更好地了解它的工作原理.

sqlJoinQuery = "SELECT tbl_grp_by.[Field1],tbl_grp_by.[Field2]," & _
                        "Switch( " & _
                            "Nz(tbl_grp_by.[maxfield3]) = 0,'0'," & _
                            "Nz(tbl_grp_by.[maxfield3]) = 1,'>1 million'," & _
                            "Nz(tbl_grp_by.[maxfield3]) = 2,'0001-0010' " & _
                        ") as [Field3]," & _
                        "tbl_grp_by.[" + commonField + "]," & _
                    "[" + tableName + "].* " & _
                    "INTO [" + newTableName + "] FROM (" & _
                        "SELECT Max([" + tableNameTemp + "].[Field1]) as [Field1]," & _
                            "Max([" + tableNameTemp + "].[Field2]) as [Field2]," & _
                            "Max(Switch( " & _
                                "Nz([" + tableNameTemp + "].[Field3]) = '0'," & _
                                "Nz([" + tableNameTemp + "].[Field3]) = '>1 million',1," & _
                                "Nz([" + tableNameTemp + "].[Field3]) = '0001-0010',2 " & _
                            "))as [maxField3]," & _
                            "[" + tableNameTemp + "].[" + commonField + "] as [" + commonField + "] " & _
                        "FROM [" + tableNameTemp + "] " & _
                        "INNER JOIN [" + tableName + "] " & _
                            "ON [" + tableNameTemp + "].[" + commonField + "] = [" + tableName + "].[" + commonField + "] " & _
                        "GROUP BY [" + tableNameTemp + "].[" + commonField + "] " & _
                    ") as tbl_grp_by " & _
                    "INNER JOIN [" + tableName + "] " & _
                        "ON [" + tableName + "].[" + commonField + "] = tbl_grp_by.[" + commonField + "]"

上面的Access查询导致此SQL字符串：

SELECT tbl_grp_by.[Field1],Switch(Nz(tbl_grp_by.[maxfield3]) = 0,Nz(tbl_grp_by.[maxfield3]) = 1,Nz(tbl_grp_by.[maxfield3]) = 2,'0001-0010') AS [Field3],tbl_grp_by.[Finding ID],[Issue_Management_Findings].* INTO [region_Issue_Management_Findings]
FROM
  (SELECT Max([temp2_temp_Issue_Management_Findings].[Field1]) AS [Field1],Max([temp2_temp_Issue_Management_Findings].[Field2]) AS [Field2],Max(Switch(Nz([temp2_temp_Issue_Management_Findings].[Field3]) = '0',Nz([temp2_temp_Issue_Management_Findings].[Field3]) = '>1 million',Nz([temp2_temp_Issue_Management_Findings].[Field3]) = '0001-0010',2))AS [maxField3],[temp2_temp_Issue_Management_Findings].[Finding ID] AS [Finding ID]
   FROM [temp2_temp_Issue_Management_Findings]
   INNER JOIN [Issue_Management_Findings] ON Nz([temp2_temp_Issue_Management_Findings].[Finding ID]) = Nz([Issue_Management_Findings].[Finding ID])
   GROUP BY [temp2_temp_Issue_Management_Findings].[Finding ID]) AS tbl_grp_by
INNER JOIN [Issue_Management_Findings] ON Nz([Issue_Management_Findings].[Finding ID]) = Nz(tbl_grp_by.[Finding ID])

因此,[Field3]在内部查询中的max()下编码,并且max在外部查询中解码.

但是,当我运行它时,我收到以下错误：

Run-time error ‘3464’: Data type mismatch in criteria expression

如果我从立即窗口中的调试输出中复制我的SQL查询并将其粘贴到手动SQL查询中(在运行我的VBA代码到运行SQL查询的断点之后),则会出现以下错误：

Data type mismatch in criteria expression

如果我只在上面的SQL字符串中运行子查询以进行调试：

(SELECT Max([temp2_temp_Issue_Management_Findings].[Field1]) AS [Field1],[temp2_temp_Issue_Management_Findings].[Finding ID] AS [Finding ID]
   FROM [temp2_temp_Issue_Management_Findings]
   INNER JOIN [Issue_Management_Findings] ON Nz([temp2_temp_Issue_Management_Findings].[Finding ID]) = Nz([Issue_Management_Findings].[Finding ID])
   GROUP BY [temp2_temp_Issue_Management_Findings].[Finding ID])

然后它运行没有错误

请注意,Issue_Management_Findings是数据库中现有表的名称.

有谁知道如何解决这些错误？

解决方法

我认为您必须在Switch上添加默认返回值,以防它无法匹配所有其他条件,因此它不会返回Null,我相信会导致数据类型不匹配问题.您可以添加…,true,“thedefaultvalue”),例如

SWITCH (field>100,"greater",field3=100,"equals","default")

所以在你的查询中.我默认为0;

sqlJoinQuery = "SELECT tbl_grp_by.[Field1]," & _
                    "Switch( " & _
                        "Nz(tbl_grp_by.[maxfield3]) = 0," & _
                        "Nz(tbl_grp_by.[maxfield3]) = 1," & _
                        "Nz(tbl_grp_by.[maxfield3]) = 2,'0001-0010' " & _
                    ",'0') as [Field3]," & _
                    "tbl_grp_by.[" + commonField + "]," & _
                "[" + tableName + "].* " & _
                "INTO [" + newTableName + "] FROM (" & _
                    "SELECT Max([" + tableNameTemp + "].[Field1]) as [Field1]," & _
                        "Max([" + tableNameTemp + "].[Field2]) as [Field2]," & _
                        "Max(Switch( " & _
                            "Nz([" + tableNameTemp + "].[Field3]) = '0'," & _
                            "Nz([" + tableNameTemp + "].[Field3]) = '>1 million'," & _
                            "Nz([" + tableNameTemp + "].[Field3]) = '0001-0010',2 " & _
                        ",0))as [maxField3]," & _
                        "[" + tableNameTemp + "].[" + commonField + "] as [" + commonField + "] " & _
                    "FROM [" + tableNameTemp + "] " & _
                    "INNER JOIN [" + tableName + "] " & _
                        "ON [" + tableNameTemp + "].[" + commonField + "] = [" + tableName + "].[" + commonField + "] " & _
                    "GROUP BY [" + tableNameTemp + "].[" + commonField + "] " & _
                ") as tbl_grp_by " & _
                "INNER JOIN [" + tableName + "] " & _
                    "ON [" + tableName + "].[" + commonField + "] = tbl_grp_by.[" + commonField + "]"

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!