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.
VBA code works without issue for numerous queries but I am running into the mentioned error for the following query:
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 adapted from: Longest prefix match in SQL Server 2000
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):
bcr.DialCode = '0841234567'
cc.CountryCode = {'084',
'0841',
'08412'}
Expected output = {'0841234567','Destination 1','08412'}
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:
SELECT
bcr.DialCode,
bcr.Destination
FROM
table1 AS bcr
Works as expected. Also:
SELECT
TOP 1 cc.CountryCode
FROM
table2 AS cc
WHERE
cc.CountryCode LIKE '1'+'%'
ORDER BY LEN(cc.CountryCode) DESC
Has no errors and outputs the expected results.
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.
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.
First of all, please read my comment to the question.
Secondly, please read these articles:
I get a message about data type mismatch
MS Access 2003: LIKE condition (using wildcards)
Finally, if you would like to compare one data to another which StartsWith("SomeString")
, you can use something like this:
SELECT
bcr.DialCode,
bcr.Destination,
cc.CountryCode
FROM table1 AS bcr
INNER JOIN table2 AS cc
ON cc.CountryCode = LEFT(bcr.DialCode, LEN(cc.CountryCode))
ORDER BY LEN(cc.CountryCode) DESC;
For further details, please see: MS Access: Left Function
Note: Do not forget to use [;
] at the end of query. MS Access database likes it ;)