Multiple Selections Help

[BenSteckler]

I have been working with a query that filters the records by dates and by using a list box selection that is located on a form. But now I am interested in selecting multiple options in the list box and filtering for all the selected options.

The help file informed me on how to change the list box to a multiple selection list box, but it does not explain how to use the multiple selections.

The query looks like this now:
Date:
>=forms![FormName]![Startdate] and <=forms![FormName]![EndDate]

Option:
=forms![FormName]![ListBox]

I would like it to filter when ever the [ListBox] = the multiple selection.

Any ideas?

 

[Alexandre]

The way to screen through a listbox and get the selected item is the following :

For Each varItm In List.ItemsSelected
..Do whatever you want with List.ItemData(varItm) that holds a selected value..
Next varItm


So this is how you would build a string (ConcatStr) that you can use as the WHERE criteria of an SQL statement (for example as the filter property of a form). If List is your control listbox:


Dim List As Control
Dim ConcatStr As String
Dim varItm As Variant

Set List = Me.controls("List")

ConcatStr = "("
For Each varItm In List.ItemsSelected
ConcatStr = ConcatStr & """" & List.ItemData(varItm) & """" & ", "
Next varItm
If ConcatStr <> "(" Then
ConcatStr = Left(ConcatStr, Len(ConcatStr) - 2) & ")"
Else
ConcatStr = ""
End If

Set list = Nothing

This example assumes that the items (varItm)in the listbox are strings. You will have to adapt it to handle other types of values. Also, imrpovement would be needed to deal with strings containing '

Alex

 

[Alexandre]

.

[This message has been edited by Alexandre (edited 09-06-2001).]

 

[Alexandre]

Hmmm...
Sorry, I was a bit quick at copying/pasting from a function I wrote and saying that it could be used directly as a filter statement. The string that is built above was originally meant to be used with an IN instruction, so you would have to concatenate ConcatStr with the proper field reference and operator:

"[MyTable].[MyField] IN " & ConcatStr

Alex



[This message has been edited by Alexandre (edited 09-06-2001).]