pass list from form to query

[mike_0_007]

Hi, I'm having a problem trying to build query selection criteria from
a form... what I'm trying to do is
pass a value from vba to a textbox for use by a query. The value is a
LIST as follows -
in('a','b','c')

the query works ok if i type the above in directly into the field. But
if i try and use the variable
i always return zero records. I've tried =, like, and various
combinations between the textbox
value and the surrounding string in the query, with no luck. Can
anyone help me out?

 

[Arvin Meyer [MVP]]

You'll have to write the query in code as a VBA select statement. Something
like:

"SELECT CostCode, ItemID, CostCodeName, ModelID FROM tblItem WHERE ModelID
In (" & Me.txtSelected & ");"

txtSelected is a hidden text box holding the value for the in clause.

 

[mike_0_007]

You'll have to write the query in code as a VBA select statement. Something
like:

"SELECT CostCode, ItemID, CostCodeName, ModelID FROM tblItem WHERE ModelID
In (" & Me.txtSelected & ");"

txtSelected is a hidden text box holding the value for the in clause.

thanks for the reply.. can you explain why this is so? i can pass
other values my way without issue.
can't pass a list?

 

[Arvin Meyer [MVP]]

thanks for the reply.. can you explain why this is so? i can pass
other values my way without issue.
can't pass a list?

I'm not absolutely sure, but I think that the query expression service reads
a variable as a single value. If you knew the maximum number of values you
had, you could probably put that many hidden text boxes on your form and do
something like:

Where ID In(Forms!Form1!Text1, Forms!Form1!Text2, Forms!Form1!Text3)

 

[mike_0_007]

I'm not absolutely sure, but I think that the query expression service reads
a variable as a single value. If you knew the maximum number of values you
had, you could probably put that many hidden text boxes on your form and do
something like:

Where ID In(Forms!Form1!Text1, Forms!Form1!Text2, Forms!Form1!Text3)

nice idea, and that method does work. I appreciate it!