CODEWITHSUNDEEP
stringvbaexcelms-access
ch-pub
ch-pub

Reputation: 1724

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

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.

Upvotes: 3

Views: 3857

Answers (2)

B540Glenn
B540Glenn

Reputation: 409

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')"

Upvotes: 3

etalon11
etalon11

Reputation: 984

Please try it with this:

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

Upvotes: -1

Related Questions