Passing multiple values using IN operator through parameterized qu

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

While passing multiple comma separated values to select query with In
operator, the query doesn't return any rows.
I.e Following query gives me reuquired resultset
Select * From Emp Where EmpName in ('Sid','Dev','Das')

But when I pass EmpNames = "'Sid','Dev','Das'", it doesn't return me any
result
Select * From Emp Where EmpName in (EmpNames)
 
As you found, you cannot pass a delimited string as a parameter and have it
parse it for you.

Where is this headed?
If it is to filter a report, you could use the WhereCondition:
strWhere = "EmpName in ('Sid','Dev','Das')"
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere

Likewise, you can use the same kind of string in the Filter of a form.

In code, you can generate the SQL string dynamically.

At worst, you can reassign the SQL property of your QueryDef after building
it dynamically:
dbEngine(0)(0).QueryDefs("MyQuery").SQL = "SELECT ...
 
Back
Top