title: VBA/ACE OLEDB/SQL Run-Time error -2147217913 Data type mismatch in criteria expression
body: <p>I am running a SQL query from VBA using ACE OLEDB without connecting to Access. I do this so I can smash together quickly some Excel tables. I am not looking to transition this to another platform so would prefer to resolve the current issue.</p>
  
  <p>VBA code works without issue for numerous queries but I am running into the mentioned error for the following query:</p>
  
  <pre><code>SELECT
      bcr.DialCode,
      bcr.Destination,
      (
          SELECT
              TOP 1 cc.CountryCode
          FROM
              table2 AS cc
          WHERE
              bcr.DialCode LIKE cc.CountryCode+'%'
          ORDER BY LEN(cc.CountryCode) DESC
      ) AS CountryCodes
  FROM
      table1 AS bcr
  </code></pre>
  
  <p>Code adapted from: <a href="https://stackoverflow.com/questions/10845035/longest-prefix-match-in-sql-server-2000">Longest prefix match in SQL Server 2000</a></p>
  
  <p>The intention is to match dialed telephone numbers to the destination country by finding the most inclusive match. Reusing the quoted text sample of input and expected results (adapted to this exercise):</p>
  
  <pre><code>bcr.DialCode = '0841234567'
  
  cc.CountryCode = {'084',
                   '0841',
                   '08412'}
  
  Expected output = {'0841234567','Destination 1','08412'}
  </code></pre>
  
  <p>From searching online seems like maybe I'm using a reserved word in the query. But I have tested different parts of the query individually and don't get any errors. For example:</p>
  
  <pre><code>SELECT
      bcr.DialCode,
      bcr.Destination
  FROM
      table1 AS bcr
  </code></pre>
  
  <p>Works as expected. Also:</p>
  
  <pre><code>SELECT
      TOP 1 cc.CountryCode
  FROM
      table2 AS cc
  WHERE
      cc.CountryCode LIKE '1'+'%'
  ORDER BY LEN(cc.CountryCode) DESC
  </code></pre>
  
  <p>Has no errors and outputs the expected results.</p>
  
  <p>The variable used to pass the query to the ADO connection is dimmed as String but I have tried setting as Variant with no change.</p>
  
  <p>Is there perhaps a difference in the implementation of SQL through ACE for which the "Longest prefix match" query doesn't work? Any guidance will be greatly appreciated.</p>