Using Parameter for IN Keyword Query

[HeyItsManny]

Hello,

I'm having an issue getting the right syntax for a query that uses the IN Keyword when I combine it with an input parameter. I'd appreciate any support. Here is some psuedo code:

This works: ... WHERE (((user.userID) In (237,240,313)))
This doesn't work if I enter the values from above using a parameter: ... WHERE (((user.userID) In ([@UserList]))) It comes back with no records. The user ID's are numbers not chars. I've tried entering the data in multiple ways such as '237', '240','313' and with extra parenthesis (237,240,313) but it doesn't work. I either get no records or a message that says the query is too complex. The query does work if I only enter one user id. I've simplified as much as possible, and I've tried searching the web but haven't found much help since "IN" is too common a word. Anyway, if you can help I'd appreciate it.

Thank you.

 

[BryanT]

Hello HeyItsManny

I may be wrong but if this is in code perhaps you need to concatenate the parameter into the statement string such as

"WHERE (((user.userID) In ([" & ParameterName & "]))) "

Bryan

 

[RV]

HeyItsManny,

if you're using your code in a static query, you need to use the OR operator.
Another option is to build a dynamic query in VBA and create the values you refer to by the IN operator using concatenation, as BryanT pointed out.

RV

 

[HeyItsManny]

Thanks everyone. I found the solution using the Instr function as documented in this microsoft KB article. ( http://support.microsoft.com/default.aspx?scid=kb;EN-US;100131 )