Paradox Paradox - 3 months ago 20
SQL Question

MS Access VBA Data Type Mismatch Error in SQL Query

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 + "]"


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])


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:


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


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


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])


Then it runs without error

Note that Issue_Management_Findings is the name of an existing table in the database.

Does anybody know how I could fix these errors?

Answer

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 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 + "]"