Multi-select list boxes

[bpcdavidson]

Hi,

I used the here instructions for the
hidden text box method (almost
exactly) and it poplulates the text box beautifully with each
selection and an "OR" between. here is the code:

Private Sub List3_AfterUpdate()

Command6.Enabled = True
Command36.Enabled = True
Command33.Enabled = True

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Me
Set ctl = frm!List3
strSQL = ""
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR "
Next varItem

'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - 4)

Me.Text43 = strSQL

End Sub

The trouble is, when i use the text box as the criteria for a query it
a only returns values if I have only chosen one item in the list box?

Am I right in thinking that the query is only returning values when
the which match
"Selection1 OR Selection2 OR Selection3" rather than "Selection1" OR
"Selection2" OR "Selection3" ?

I'm very confused, and probably way out of my depth.....Please help!!!

 

[bpcdavidson]

Sorry, I forgot which forum I was on and inserted the link wrong!
Here it is...

http://groups.google.co.uk/group/mi...24892f75b08/eca7f05765bf285d#eca7f05765bf285d

 

[Carl Rapson]

Hi,

I used the here instructions for the
hidden text box method (almost
exactly) and it poplulates the text box beautifully with each
selection and an "OR" between. here is the code:

Private Sub List3_AfterUpdate()

Command6.Enabled = True
Command36.Enabled = True
Command33.Enabled = True

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Me
Set ctl = frm!List3
strSQL = ""
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR "
Next varItem

'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - 4)

Me.Text43 = strSQL

End Sub

The trouble is, when i use the text box as the criteria for a query it
a only returns values if I have only chosen one item in the list box?

Am I right in thinking that the query is only returning values when
the which match
"Selection1 OR Selection2 OR Selection3" rather than "Selection1" OR
"Selection2" OR "Selection3" ?

I'm very confused, and probably way out of my depth.....Please help!!!

When using the OR clause, you have to repeat the field name for each value.
Something like:

strSQL = ""
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & "[MyField]=" & ctl.ItemData(varItem) & " OR "
Next varItem
strSQL = Left$(strSQL, Len(strSQL) - 4)

Note also that if your listbox values are strings instead of numbers, you'll
need to add quotes around each value:

strSQL = ""
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & "[MyField]='" & ctl.ItemData(varItem) & "' OR "
Next varItem
strSQL = Left$(strSQL, Len(strSQL) - 5)

Alternately, you can use an IN clause:

strSQL = "[MyField] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & ","
Next varItem
strSQL = Left$(strSQL, Len(strSQL) - 1) & ")"

Again, you'd need extra quotes around each value if the values are strings
instead of numbers.

Carl Rapson