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

我目前有以下 MS Access SQL 查询，它是 Access VBA 函数的一部分。它是在以下人士的帮助下建造的上一个问题 https://stackoverflow.com/questions/38903010/how-to-sql-join-tables-selecting-the-largest-value-in-access-vba-function，您可以查看它以更好地了解其工作原理。

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' , 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],
       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.[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', 0, Nz([temp2_temp_Issue_Management_Findings].[Field3]) = '>1 million', 1, 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 在外部查询中进行解码。

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

运行时错误“3464”：条件表达式中的数据类型不匹配

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

标准表达式中的数据类型不匹配

如果我仅出于调试目的在上面的 SQL 字符串中运行子查询：

(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', 0, Nz([temp2_temp_Issue_Management_Findings].[Field3]) = '>1 million', 1, 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])

然后运行就没有错误了

注意问题_管理_调查结果是数据库中现有表的名称。

有谁知道我该如何修复这些错误？

我认为你必须在你的Switch以防万一它无法匹配所有其他条件，因此它不会返回Null我相信这会导致Data Type mismatch问题。您只需添加...,true,"thedefaultvalue") e.g.

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

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

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' " & _
                    ", true,'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' , 0, " & _
                            "Nz([" + tableNameTemp + "].[Field3]) = '>1 million' , 1, " & _
                            "Nz([" + tableNameTemp + "].[Field3]) = '0001-0010', 2 " & _
                        ", true, 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 + "]"