SQL查询中的MS Access VBA数据类型不匹配错误 [英] MS Access VBA Data Type Mismatch Error in SQL Query
问题描述
我目前有以下MS Access SQL查询,它是Access VBA功能的一部分。它是在先前的问题,您可以查看该问题以更好地了解其工作原理。
I currently have the following MS Access SQL Query which is part of an Access VBA function. It has been built with help from a previous question, which you can look at to better understand how it works.
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字符串:
The above Access query results in this SQL String:
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被解码在外部查询中。
So [Field3] is encoded under max() in the inner query and that max is decoded in outer query.
但是,当我运行它时,出现以下错误:
However, when I run it I get the following error:
运行-time error'3464':条件表达式中的数据类型不匹配
Run-time error '3464': Data type mismatch in criteria expression
如果我在即时窗口中从调试输出中复制了我的SQL查询,并且将其粘贴到手动SQL查询中(在将我的VBA代码运行到应该在其中运行SQL查询的断点之后),然后出现以下错误:
If I copy my SQL query from debug output in the immediate window and paste it in a manual SQL query (after running my VBA code up to a breakpoint where the SQL query should be run), then I get the following error:
条件表达式中的数据类型不匹配
Data type mismatch in criteria expression
如果仅出于调试目的在上述SQL字符串中运行子查询,则: / p>
If I only run the subquery in my above SQL string for debugging purposes:
(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])
然后它运行无误
请注意,问题管理发现是数据库中现有表的名称。
Note that Issue_Management_Findings is the name of an existing table in the database.
有人知道我该如何解决这些错误?
Does anybody know how I could fix these errors?
推荐答案
我认为您必须在 Switch
上添加默认返回值,以防万一它不符合所有其他条件,不会返回 Null
,我认为这会导致数据类型不匹配
问题。您可以只添加 ...,true, thedefaultvalue)
例如
I think you have to add default return value on your Switch
just in case it fails to match all the other criteria so it wont return Null
which I believe cause the Data Type mismatch
issue. You can just add ...,true,"thedefaultvalue")
e.g.
SWITCH (field>100, "greater", field3=100 ,"equals", true, "default")
so在您的查询中。我默认将其设置为 0
;
so in your query. I default it to 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 + "]"
这篇关于SQL查询中的MS Access VBA数据类型不匹配错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!