retrieve data based on a multi-select list box?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
does anyone have an example of how to do this- i have a list box on a form.
user wants to select more than one item in list. the query related to this
form is supposed to pull data based on the users input on the form (this list
box is only one of the inputs) . but how do i translate the multiple
selection into the query , so that it is doing something like
(field)=(selected item 1) OR (selected item 2) OR... etc
 
This is rather tricky. I've found that the easiest way is to use the IN
statement, so your WHERE clause would look like "field IN (item1, item2...)"

I have a standard function I use for just this purpose. The function
requires a string and listbox input, and returns a string of all the selected
items in the listbox, separated by commas. I then use this string with the IN
statement in a WHERE clause.


#######################################
BEGIN CODE
#######################################

Public Function buildList(strString As String, lstList As ListBox) As String
Dim i As Integer
For i = 0 To lstList.ListCount - 1
If lstList.Selected(i) Then
If strString = "" Then
strString = Chr$(39) & lstList.ItemData(i) & Chr$(39) 'use
me for text
strString = lstList.ItemData(i) 'use me for numeric
Else
strString = strString & "," & Chr$(39) & lstList.ItemData(i)
& Chr$(39) 'use me for text
strString = strString & "," & lstList.ItemData(i) 'use me
for numeric
End If
End If
Next i
buildList = strString
End Function

##################################

Note that in the above code I've included two assignment statements in both
the if and the else -- the ones with Chr$(39) will insert quotes around the
ItemData -- use this version if the listbox contains text. Note that I use
the Chr$(39) function to assign quotes around the listbox text just in case
the text itself will contain quotes.

Use the other version for numeric data.


I then call it like this
dim strS as string, strT as string
strS = "SELECT * FROM tbl "
strS = strS & "WHERE fld IN (" & buildList(strT, myListbox) & ")"

If you have other criteria to go into the WHERE clause, you can keep
appending ANDs and ORs.

I've used this code over and over on all kinds of forms, and it works slick.
Let me know if anything isn't clear.
 
ok, thanks Mike, this is very helpful. i will try it out, and write back if i
get stuck.
 
HELP
i am still having problems with this. i can build the string, but when i try
to paste this into the SQL in the underlying query, it doesnt seem to work.
also, i tried populating an unbound box on my form with the string, and
having the query use the contents of that box as criteria, and dont
understand why it wont work from the control, but if i copy/paste the
contents of the control into the query, then it works... this is very
frustrating. one of those times when i resent the fickleness of Access!!
 
Back
Top