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?

Pentastich

Not sure on VBA syntax, but what * means here "Max(Switch( " * _ ? – Yt

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. – Keeper

@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. – Pentastich

Also, I have fixed a missing comma after [maxfield3] = 2 and updated OP with it. There is still an error message though. – Pentastich

@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. – Pentastich

This is primarily a debugging help for you - if you can run your VBA code, you have sqlJoinQuery and can Debug.Print it, and copy it into a query. – Keeper

@Andre: I have just done that, however when I run the query it tells me that You have tried to execute a query that does not include the specified expression 'Field4' as part of an aggregate function. - updated OP – Pentastich

That's correct, in the inner query you are missing the Max() for [Field4] as you have it for [Field1] and [Field2]. Or any aggregation function that is applicable. – Keeper

Andre: I am using Field4 in the join between the two tables. Will this mess up the Field4's value if I take a max (you can see how Field4 is being used here)? – Pentastich

Is Field4 different from commonField? From our last discussion i had the impression they are actually same field. If not, change the Field4 to commonField since it is the one you used on the latter inner join and specified on group by clause. If it is the same, theres some typo involve – Gamez

@winghei: yes, they are the same field. I will update OP to remove ambiguity. – Pentastich

Andre: I have updated OP with the full SQL string. The error messages are still present. – Pentastich

Please use sqlformat.org plus manual corrections to get readable SQL. There is no Field4 in your SQL, so the error msg can't be correct. You have a .[Findin g ID] in your SQL. – Keeper

Andre: I have updated my SQL Code formatting, hopefully this is sufficient. As winghei mentioned, my commonField field is the same as Field4, so I removed Field4 altogether for simplicity. the commonField variable's value in this case is "Finding ID". – Pentastich

The .[Findin g ID] is probably from Debug.Print line wrap. Make sure you correct this in your SQL that you paste into a query. And in your question. :) – Keeper

Andre: Thank you, I have corrected this and updated SQL query as well as the error message. – Pentastich

Now we're back to debugging. "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." If this doesn't help, simplify. Run the subquery (tbl_grp_by) on it's own. – Keeper

After running the query, no specific part was highlighted. To only run the subquery, should I just run the first five lines of my SQL SELECT String? – Pentastich

Updated question with SQL and related error. Does this mean that in Access SQL I cannot select all fields in the table with a wildcard in my query? – Pentastich

Dont forget to put brackets on the last query u tried to debug [issue_finding_management] . Seems to be fine on the other though. – Gamez

Erm no, I meant the subquery in parentheses from after the main FROM to AS tbl_grp_by – Keeper

For Data mismatch issue, are you sure no null values were return when u use switch on your maxField3. Try add Nz(tbl_grp_by.maxFeld3) = 0, ... – Gamez

winghei: In my vba code at the very top, this is already being done: ` "[" + tableName + "].* " & _` If I add the brackets directly to the SQL that I am manually testing, then save and view it again, they are automatically removed by Access. Andre: I will make that change, run it, and update OP now. – Pentastich

The subquery is the part inside the parentheses. i.e. a normal SELECT statement. – Keeper

I see . But for data type mismatch, try to check if you have null values on expressions like =. I usually had that issue for null fields where i use NZ(field). Just a hint – Gamez

winghei: I have added in the Nz() function and this appears to have fixed all of my errors. You are welcome to go ahead and post an answer. – Pentastich

@Paradox. i think return a default value instead on your Switch which I believe is more appropriate. Nz will return to 0 or "" base on data type you are comparing it to. So if you want to default ">1 Million" for those null it wont be possible on your current expression. – Gamez

Answer 1

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

Gamez