Excel VBA, Syntax error (missing operator) in query expression

Question

I'm attempting to retreive a resultset from a MS Access database using VBA for Excel. In the VBA code, I'm constructed a string equal to:

strSql = "SELECT * FROM Pricing WHERE Account In (''1234'', ''ABCD'') '; "

Note that there are 2 single quotes around the strings within the SQL statement. There is also a single quote before the semi-colon. If I'm not mistaken, this evaluates to:

SELECT * FROM Pricing WHERE Account In ('1234', 'ABCD') ;

This query works fine when run directly in MS Access. However, in Excel VBA, I keep getting the Run-time error:

Syntax error (missing operator) in query expression 'Account In (''1234'', ''ABCD'') '; '

Notice that this error actually cut off the first half of the SQL statement.

I've tried a few variations, using double-quotes, double double-quotes, no quotes, etc. etc.

Any advice?

Thanks.

There seems to be an un-matched quote right before your semi-colon. What if you remove that? What happens if you make the statement "SELECT * FROM Pricing WHERE Account In ('1234', 'ABCD');"? — Monkpit
Add Debug.Print strSql, run the code and see what it shows you in the Immediate window. — HansUp
@HansUp This is really what solved it for me. Admittedly, I'm quite poor at debugging in Excel VBA. Thanks! — ch-pub
Good! If one of those target values actually included a single quote ... 3 o'clock for example ... you would need to double up that quote like this In ('abc', '3 o''clock') But more generally, always verify that the text of the SQL statement your code builds matches what you expect --- and that's where Debug.Print and the Immediate window can be useful. — HansUp

B540Glenn B540Glenn · Accepted Answer · 2016-01-11T19:02:35

In Excel VBA the string identifier is the " (double quote) character. You do not need to double the single quote characters for them to pass through to the database when enclosed by double quotes.

Try this:

strSql = "SELECT * FROM Pricing WHERE Account In ('1234', 'ABCD')"

Excel VBA, Syntax error (missing operator) in query expression

2 Answers