Error accessing VBA MS Access data in SQL Query

Question

I currently have the following SQL Access SQL Query query, which is part of the VBA access function. It was created using the previous question , which you can look at to better understand how this 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 internal request and that max is decoded in the external request.

However, when I run it, I get the following error:

Runtime Error '3464': Data Type Mismatch in Criteria Expression

If I copy my SQL query from the debug output to the nearest window and paste it into a manual SQL query (after running my VBA code to the breakpoint where the SQL query should be executed), I get the following error:

Data type mismatch in criteria expression

If I just run a subquery in my above SQL line 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 starts without errors

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

Does anyone know how I can fix these errors?

Answer 1

I think you need to add the default return value to your Switch just in case it does not meet all other criteria, so it will not return Null , which I think causes the Data Type mismatch problem. You can just add ...,true,"thedefaultvalue") for example.

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

therefore in your request. 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 + "]"