Parameter for an IN Stored Query

[russellobrien]

Just a quickie,

I am trying to use a stored query such as:

SELECT * FROM TABLE1 WHERE FACTOR1 IN (@List);

I then programmatically enter @List as 1,2,3,4,5 etc. However, it doesn't like this and seems to report an empty IN () clause.
Any help appreciated!

Russ

 

[mpastore]

You can do something like this (this assumes datatype of factor1 is char()):

Sub in_qry()

Dim sSQL As String
sSQL = &quot;SELECT * FROM TABLE1 WHERE FACTOR1 IN (<<LIST>>)&quot;

Dim sList As String
sList = &quot;'AAA','BBB'&quot;

sSQL = Replace(sSQL, &quot;<<LIST>>&quot;, sList)

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(sSQL)


End Sub