Multiselect List and IN Statement in Query

[Steven Summers]

I am tring to wire up a form with a multiselect listbox to a query. I use
the following code to populate a hidden textbox:

Dim stDocName As String

Dim ctlList As Control, varItem As Variant

txtCenter.Value = ""

' Return Control object variable pointing to list box.
Set ctlList = Forms!ELSummary!lstcenter
' Enumerate through selected items.
For Each varItem In ctlList.ItemsSelected

txtCenter.Value = txtCenter.Value & "'" & ctlList.ItemData(varItem)
& "', "

Next varItem
txtCenter.Value = "In(" & Left(txtCenter.Value, Len(txtCenter.Value) - 2) &
")"

When I tie my query to this control, the query doesn't return any rows of
data. If I hard code the IN('4200102','4200211') in the query, it works
fine. Am I missing something here?

Thanks for any help you may offer.

Steven Summers

 

[Dale Fye]

Steven,

It appears that what you have done should work. What does the rest of
the code look like? What is displayed in txtCenter after this segment
of the code?

BTW, I usually use a variant variable, and preappend my commas as
shown below. That way, I don't have to strip the commas after the
loop.

Dim stDocName As String
Dim varSQL as variant
Dim ctlList As Control, varItem As Variant

varSQL = Null

' Return Control object variable pointing to list box.
Set ctlList = Forms!ELSummary!lstcenter
' Enumerate through selected items.
For Each varItem In ctlList.ItemsSelected

varSQL = varSQL + "," & "'" & ctlList.ItemData(varItem) & "'"

Next varItem
txtCenter.Value = "In(" & varSQL & ")"


--
HTH

Dale Fye


I am tring to wire up a form with a multiselect listbox to a query. I
use
the following code to populate a hidden textbox:

Dim stDocName As String

Dim ctlList As Control, varItem As Variant

txtCenter.Value = ""

' Return Control object variable pointing to list box.
Set ctlList = Forms!ELSummary!lstcenter
' Enumerate through selected items.
For Each varItem In ctlList.ItemsSelected

txtCenter.Value = txtCenter.Value & "'" &
ctlList.ItemData(varItem)
& "', "

Next varItem
txtCenter.Value = "In(" & Left(txtCenter.Value, Len(txtCenter.Value) -
2) &
")"

When I tie my query to this control, the query doesn't return any rows
of
data. If I hard code the IN('4200102','4200211') in the query, it
works
fine. Am I missing something here?

Thanks for any help you may offer.

Steven Summers

 

[Steven Summers]

I'm basicly setting the control to the varible. I can see the correct
syntax in the value of the textbox. But for some reason, when I issue my
query, it returns 0 rows. I tested the same query with the IN hard coded
and it works. Wierd hun..

I will try the variant an see if that makes a difference. Thanks for your
reply.

 

[Marshall Barton]

I am tring to wire up a form with a multiselect listbox to a query. I use
the following code to populate a hidden textbox:

Dim stDocName As String

Dim ctlList As Control, varItem As Variant

txtCenter.Value = ""

' Return Control object variable pointing to list box.
Set ctlList = Forms!ELSummary!lstcenter
' Enumerate through selected items.
For Each varItem In ctlList.ItemsSelected

txtCenter.Value = txtCenter.Value & "'" & ctlList.ItemData(varItem)
& "', "

Next varItem
txtCenter.Value = "In(" & Left(txtCenter.Value, Len(txtCenter.Value) - 2) &
")"

When I tie my query to this control, the query doesn't return any rows of
data. If I hard code the IN('4200102','4200211') in the query, it works
fine. Am I missing something here?

Yes, you are ;-)

IN expects a list of individual values, not a reference to a
string that may (or may not) look like a list of values.
Furthermore, a criteria in a query (usually) consists of
three parts, the left operand, an operator and the right
operand. It looks like you're trying to combine the
operator and the right operand into a single string.

One way to do this kind of thing is to modify the query's
SQL property to place your string into the query. This is
rarely really necessary since the most common use of queries
is as the record source of a form or report. Fortunately,
if that what's you're doing, you can place an SQL string
directly into the form/report's RecordSource property.

 

[Dale Fye]

See Marshall's note.

That is why I asked to see the rest of your code. In never occurred
to me that you might be referring to the textbox in your query, which
is what I think Marshall is implying.

--
HTH

Dale Fye


I'm basicly setting the control to the varible. I can see the correct
syntax in the value of the textbox. But for some reason, when I issue
my
query, it returns 0 rows. I tested the same query with the IN hard
coded
and it works. Wierd hun..

I will try the variant an see if that makes a difference. Thanks for
your
reply.

 

[Steven Summers]

Yes, I do want it for a report. Thanks for this tip. If works flawlessly
now!
Steve