list box to query question

[Jennyrd]

I have a multi select listbox that feeds out to a text box so that in the
text box all of the chosen values are separated by "Or", ie: CPC Or MON Or
US. I have tried separating by commas, by Or with ' around the text, by Or
Like and the text box still will not feed into the query to give any results.
If I choose one item from the list box, feed it to the text box and run the
query it works. I even typed in the values to the text box exactly as they
should be with quotes and all and tried running the query, still no results.
Is there something I'm missing?

Here's the code that sends the list box to the text box:

Private Sub Test_Button_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strTemp As String
Set frm = Me
Set ctl = frm!List76
For Each varItem In ctl.ItemsSelected
strTemp = strTemp & ctl.ItemData(varItem) & " Or "
Next varItem
strTemp = Left$(strTemp, Len(strTemp) - 4)
Me![Function_Report] = strTemp
End Sub

This is what I have in the query's criteria line:

Like [Forms]![Report1]![Function_Report]

Thanks for any help you can provide!
-Jenny

 

[Brotha Lee]

Jennyrd,

If the criteria you select in the listbox exactly match the data in the
table the Where clause of your SQL string should be like (doesnt matter of
the field type of underlying table is numeric or string):
WHERE (Field1 LIKE 'Criteria1' or 'Criteria2'). By the way if this is the
case isnt it easier to use the IN as operator (IN(Criteria1, Criteria2))

If it is the case that the criteria does not exactly match you should create
a where clause has wild card in there.

For example if you want to have all field starting with 'ha' you should set
up the where clause as: Field1 like 'ha*'.

HTH

 

[Jennyrd]

Thanks for answering. I've tried the In statement, but I want my text box to
default to * when there is no value selected. My listbox data exactly
matches the data in the table because it is populated by a query off the
table. I have a button that takes the choices selected in the listbox,
concatenates them and populates a text box with the concatenated list of
items. The query then references this list box as the list of data to filter
on. The problem is that when I choose more than one value, the query comes
up with no results. If I choose one value it comes up with results, if I
copy the list from the text box and paste it into the query it comes up with
results. My question is why is the query not seeing my textbox when there is
more than one value in it? Thanks for any additional help provided!!

Jennyrd,

If the criteria you select in the listbox exactly match the data in the
table the Where clause of your SQL string should be like (doesnt matter of
the field type of underlying table is numeric or string):
WHERE (Field1 LIKE 'Criteria1' or 'Criteria2'). By the way if this is the
case isnt it easier to use the IN as operator (IN(Criteria1, Criteria2))

If it is the case that the criteria does not exactly match you should create
a where clause has wild card in there.

For example if you want to have all field starting with 'ha' you should set
up the where clause as: Field1 like 'ha*'.

HTH

I have a multi select listbox that feeds out to a text box so that in the
text box all of the chosen values are separated by "Or", ie: CPC Or MON Or
US. I have tried separating by commas, by Or with ' around the text, by Or
Like and the text box still will not feed into the query to give any results.
If I choose one item from the list box, feed it to the text box and run the
query it works. I even typed in the values to the text box exactly as they
should be with quotes and all and tried running the query, still no results.
Is there something I'm missing?

Here's the code that sends the list box to the text box:

Private Sub Test_Button_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strTemp As String
Set frm = Me
Set ctl = frm!List76
For Each varItem In ctl.ItemsSelected
strTemp = strTemp & ctl.ItemData(varItem) & " Or "
Next varItem
strTemp = Left$(strTemp, Len(strTemp) - 4)
Me![Function_Report] = strTemp
End Sub

This is what I have in the query's criteria line:

Like [Forms]![Report1]![Function_Report]

Thanks for any help you can provide!
-Jenny