Parameter Query Using "in qUALIFIER"

CAPETOWNANDRE

New member
Local time
Today, 03:25
Joined
Aug 9, 2009
Messages
2
I am using vba to call queries, but I cannot get to pass in a list of elements into the parameter.

how do you seperate the diffrent elements
I hav a Text parameter MYparm

I have a query which reads

/code
select * from mytable
where myvalue in ([myparm]);
/*

if I enter a single value into the parm it works ok
But I cannot enter more than one value
I need to be able to enter value1,value2,value3
 
I am using vba to call queries, but I cannot get to pass in a list of elements into the parameter.

how do you seperate the diffrent elements
I hav a Text parameter MYparm

I have a query which reads

/code
select * from mytable
where myvalue in ([myparm]);
/*

if I enter a single value into the parm it works ok
But I cannot enter more than one value
I need to be able to enter value1,value2,value3
let me count the ways that this question has been asked...;)

the answer you're looking for is in the 'OR' keyword. say you have 3 possible input values for params, but you want to put ANY # of params in that you wish at any given time. this will cover your basis...
Code:
WHERE ((field = [param1] OR field IS NULL) AND 
   (field = [param2] OR field IS NULL) AND 
      (field = [param3] OR field IS NULL))
not filling in any of the params will result in the entire datasheet being displayed.
 
The reason why IN([MyParam]) doesn't work is because IN() does not mean "parse whatever string I hand to you", but only "find a value in this list". More specifically, IN() is a compile-time shortcut for our convenience which gets resolved into "X = A OR X = B OR X = C".

Adam already gave you a solution; there's also the option of executing a dynamic query:

Code:
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim s As string

s = "A, B, C"

Set qdf = CurrentDb.QueryDefs("MyInQuery")

With qdf
   .SQL = "SELECT * FROM aTable WHERE X = IN(" & s & ");"
End With

'Do whatever you wanted to do with the query; open a form/report bound to it or open a recordset

The reason why this code just gave works because it creates a new query on the fly and encodes the IN() with literal values (e.g. IN(A, B, C) rather than a IN([param]), so it get resolved correctly.
 

Users who are viewing this thread

Back
Top Bottom