MS Access VBA Data Type Mismatch Error in SQL Query

Question

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?

Not sure on VBA syntax, but what * means here "Max(Switch( " * _ ? — Serg
Copy&Paste your SQL string into a new query (SQL view) and run it there. Hopefully, a specific part of the SQL will be highlighted as error. — Andre
@Serg: That should have been an & _ to continue the line ( * is next to & on the keyboard). I have replaced it and will update OP with new error. — Paradox
Also, I have fixed a missing comma after [maxfield3] = 2 and updated OP with it. There is still an error message though. — Paradox
@Andre: I will try to recreate the string, thought it may take some time before I post output since my query has several variables whose states I will have to recreate manually. — Paradox

winghei winghei · Accepted Answer · 2016-08-15T16:32:44

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

MS Access VBA Data Type Mismatch Error in SQL Query

1 Answers