SQL Query runs as pass-through but VBA returns error

[icyrius]

I would great appreciate if you can provide any guidance with this error. I have a VBA SQL query using a CASE WHEN statement that looks like this,

If I print the .commandtext string and paste this query in Access, it would return the answer that I need when run as pass-through. However since this is a loop reading multiple dates and locations, it must run using an ODBC connection using VBA. The error I receive is run-time error (missing operator) in query expression 'MORE THAN 15'.

I tried converting if to IIF and SWITCH, but since I do not have a lot of experience with those functions, they would also return errors pointing to VOLUME_UNITS.

Any help will be greatly appreciated. This is driving me insane.

[pjwhitfield]

can you paste the string thats created into here?

[mrice]

Do you need the CASE statement in brackets?

[icyrius]

@pjwhitfield

There is the string generated by code

Again, this query would run in access as pass-through without any issues.

@mrrice

Yes, since I am naming the results as TotalItems

Thanks for the help.