I have a list box on a form which allows me to choose multiple selections. I have a query whose criteria is set to look at the listbox for information. My problem is if I choose just one item from the list box the query works. If I choose more than one item the query returns nothing. I have watched in the debug window and my items are both listed with a "," as a seperator. (For Example complaint,inquiry) My query criteria is "In ([Form]![ListboxQuery]![lstSubject])" I have tried to put quotes around each of the items and this does not work either. Can someone tell me what I am missing?
List Box multiple pick to Query criteria
- Thread starter Blkblts
- Start date
Travis
Registered User.
- Local time
- Today, 13:15
- Joined
- Dec 17, 1999
- Messages
- 1,332
Multi select in a List box does not work the way some feel it should, but if thought logically it makes sense. First Only one value can be stored in a field and since a multiselect allows for more then one value Access does not know which to store so being a logical sort of chap it stores none of them. But before you worry to much there is a way to find out what has been selected:
Dim ctl As Control
Dim varItm As Variant
Dim stItem As String
Set ctl = Me!lstParameters
For Each varItm In ctl.ItemsSelected
stString = stString & iif(stString="",""," or ") & ctl.Column(0, varItm)
Next varItm
This will loop through the lstParameters control and parse the selected items into one string. You will need to play with adding "and / or" between the items to then send that string to your query. You may need to populate another text box on your form. Have it update by placing this code on the afterupdate of the list box and pointing the query to the text field. For the users make the visible property False That way they think its the list box.
Dim ctl As Control
Dim varItm As Variant
Dim stItem As String
Set ctl = Me!lstParameters
For Each varItm In ctl.ItemsSelected
stString = stString & iif(stString="",""," or ") & ctl.Column(0, varItm)
Next varItm
This will loop through the lstParameters control and parse the selected items into one string. You will need to play with adding "and / or" between the items to then send that string to your query. You may need to populate another text box on your form. Have it update by placing this code on the afterupdate of the list box and pointing the query to the text field. For the users make the visible property False That way they think its the list box.
