Missing Operator in Query Expression

**dollylectric** · 12-18-2012, 08:11 PM

I've been going crazy trying to figure this out. I keep getting a syntax error (Missing Operator in Query Expression) and I don't know why. This query works perfectly when I run it in Access (and the query is, in fact, as you'll see in the code I'm posting, querying the same Access 2007 database I tested it on). I'm more used to Access VBA than Excel VBA, so this may be due to some difference in the SQL syntax for Excel that I don't know about. Or maybe it's because I used the Access syntax for a paramaterized query? Anyway, here's the code (I've hilighted the SQL statement in red) - any help would be greatly appreciated:

Please Login or Register  to view this content.

**Norie** · 12-18-2012, 09:26 PM

I don't know if it's causing the problem but there's no space between Concat(Left(Doctor.Last_Name, 1), Left(Doctor.First_Name, 1)) and MRR.MRR001.

Please Login or Register  to view this content.

If this code works in Access it will work in Excel.

Well the SQL part anyway.

**dollylectric** · 12-19-2012, 01:25 PM

Found the problem! There was a missing space before the FROM keyword. Queries this long/complicated at the end of the day are just asking for a headache.

So, thanks, Norie. While your answer didn't exactly pinpoint the problem, it did point me in the right direction: look for missing spaces at the beginning/end of lines.

The parameterized portion doesn't work in Excel though, I get an error telling me that values for the parameters are missing, i.e., Excel is not prompting for the parameter values the way Access would. I'm going to try to set up some cells where a user can input the information then reference those cells in the SQL. Hopefully that will work.

Wish me luck!

**Norie** · 12-19-2012, 01:33 PM

Does that code work in Access and ask the user for the parameters?

PS I knew there were other missing spaces, just thought you might enjoy finding them.

**dollylectric** · 12-19-2012, 02:14 PM

I've never tried it through VBA, but if you're just creating a query in Access (embedded or stand-alone), you create parameters in the WHERE clause by enclosing the parameter in brackets (you'll get an input box and whatever you put in those brackets is the prompt the user will see in the input box).

I'm guessing that if you do it through VBA you have to do it the old fashioned way and create an input box for user input then pop that input into the query string. I'll test it.

PS: There was another error in my code: it's "ActiveSheet", not "ActiveWorksheet".

**Norie** · 12-19-2012, 02:20 PM

Ah, thought you might have been referring to a saved query.

If you do it using VBA/ADO you have to code for the parameters yourself.

You have various options for user input, cells, inputboxes, a small userform perhaps.

Once you have the input you either need to incorporate it in the SQL statement or use the Parameters collection.