Solved Syntax Error Missing Operator question (1 Viewer)

[Abhorsen]

Hello guys, I am trying to load a csv to memory in Powershell and query it using an OLEDB driver, and have done this successfully in the past however for this specific query I am getting a syntax error. My query is as follows:
SELECT * from csv WHERE [sAMAccountName] NOT IN( $removeList )

A similar styled query in another script I regularly use works in this format as $removeList is a single string variable as seen in the error below, however the other script uses WHERE [sAMAccountName] IN ( $otherList ) rather than NOT IN.

Error:
Syntax error (missing operator) in query expression '[sAMAccountName] NOT IN( 'user1', 'user2', 'user3', 'user4' ...'

Would anyone know what operator is missing or if the query is valid as I was under the impression NOT IN is valid in Access SQL?

Thanks in advance,

Chris

 

[CJ_London]

Would anyone know what operator is missing or if the query is valid as I was under the impression NOT IN is valid in Access SQL?

look for a missing ' or ) at the end as far as the 2nd query is concerned

NOT IN is perfectly valid - you are missing a space after IN which might make a difference.

but your first example won't work in Access, you need to state the list (per your 2nd query)

 

[Abhorsen]

look for a missing ' or ) at the end as far as the 2nd query is concerned

NOT IN is perfectly valid - you are missing a space after IN which might make a difference.

but your first example won't work in Access, you need to state the list (per your 2nd query)

You're joking, the one space made the difference, nothing else changed and it works. My previous query worked with [sAMAccountName] IN( $otherList ) so I assumed (I guess incorrectly?) that a space between IN and ( wouldn't matter. Weird. Thanks mate, that did the trick though. Much appreciated!

 

[The_Doc_Man]

Sometimes Access sees those parentheses flush against something else and thinks it is function syntax. Sometimes it has enough cues to decide that it ISN'T a function. Quite often, it is a matter of guesswork to figure out why. However, adding a single space seems to be a reasonable price to pay for a fix.

 

[gemma-the-husky]

I put extra spaces in everywhere in SQL, to make sure there is no possibility of confusion. or ambiguity.

VBA sometimes adds the correct formatting separators, and sometimes it doesn't. I think I recall similar issues when typing into the query pane.
It's just easier to add them, as it can't be a mistake.