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');"? — Kyle Pittman
– Kyle Pittman, Commented Jan 11, 2016 at 18:59
Add Debug.Print strSql, run the code and see what it shows you in the Immediate window. — HansUp
– HansUp, Commented Jan 11, 2016 at 19:02
@HansUp This is really what solved it for me. Admittedly, I'm quite poor at debugging in Excel VBA. Thanks! — GNUser
– GNUser, Commented Jan 11, 2016 at 19:17
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
– HansUp, Commented Jan 11, 2016 at 19:27

B540Glenn · Accepted Answer · 2016-01-11 19:02:35Z

3

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

answered Jan 11, 2016 at 19:02

B540Glenn

4092 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GNUser Over a year ago

This works, if it also includes the semi-colon at the end.

etalon11 · Accepted Answer · 2016-01-11 19:01:46Z

-1

Please try it with this:

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

answered Jan 11, 2016 at 19:01

etalon11

9943 gold badges15 silver badges40 bronze badges

3 Comments

Sumurai8 Over a year ago

Please do not post code-only answers. They are useless for future visitors.

Magisch Over a year ago

Code only answers are unlikely to help future visitors. Please consider expanding your answer to include an explaination as to what you changed and why this code of yours works.

etalon11 Over a year ago

Thanks for this hint. I will do this in the future.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related