pass variable to criteria

[Wrathchild]

Hi, I would like to pass a variable to criteria in a query. The variable can have more than one entry separated by a comma. For example one result of the var could be 1,4. I would like to pass that to the criteria as In(1,4) so I used In(varname()) but it doesn't return the data as when I manually put In(1,4) in the criteria. I've checked the var to make sure it's returning 1,4 from the function and it is.

 

[devnull69]

I think for doing this you need to put the whole SQL-String together manually like this (Access-DAO):

Dim SQLString As String
Dim qd As QueryDef
Dim db As Database
Dim rs As Recordset

'First check for multiple values
If InStr(Variable, ",&quot > 0 Then
SQLString = "SELECT xyz FROM abc WHERE id IN(" & Variable & ""
Else
SQLString = "SELECT xyz FROM abc WHERE id = " & Variable & ";"
End If

Set db = CurrentDb
Set qd = db.QueryDefs("NameOfQuery&quot
qd.Sql = SQLString
Set rs = qd.OpenRecordset(dbOpenSnapshot)
'Process recordset data
...
rs.Close
Set rs = Nothing
Set qd = Nothing