Excel ODBC SQL Statement Throwing Error '[ODBC EXCEL Driver] Data Type Mismatch in criteria

0
votes

The SQL statement below inserts values into a new table, in a new sheet (TempPoints). If I remove the last part of the statement (in bold) the query runs OK however when I add it back in to the statement I receive the following error message; '[ODBC EXCEL Driver] Data Type Mismatch in criteria'.

I have replicated the tables and query in SQLServer and it runs OK and as expected which proves the full statement is OK. Is this a limitation of the Excel ODBC driver??

Thank you in advance.

strSQL = "INSERT INTO [TempPoints$] (REFP, PointItemRef, LoadItemRef, PointDES, Iotype, Subtype, Notes) " & _
               "SELECT " & tempref & ", [PointsDB$].ItemRef, [LoadstoPointsDB$].LoadRef, [PointsDB$].PointDES, [PointsDB$].Iotype, [PointsDB$].Subtype, [PointsDB$].Notes " & _
               "FROM [LoadsToPointsDB$] INNER JOIN [PointsDB$] ON [LoadsToPointsDB$].PointRef = [PointsDB$].ItemRef " & _
               "WHERE [LoadsToPointsDB$].LoadRef = " & moditemref & " AND [PointsDB$].SystemComp = 'Y' " & _
               **"AND NOT EXISTS (SELECT * FROM [TempPoints$] WHERE [TempPoints$].PointItemRef = [PointsDB$].ItemRef)"**
1
sqlexcelodbc

1 Answers

0
votes

Thank you for those that viewed this, but I seem to have found the answer.

There is a previous insert into statement that loads data in the table, once the data was being loaded into it, the table was storing the numbers as text and not integers. After removing the rows and ensuring the format of the columns was set to integer the SQL query began to function as expected.