Filtering multiple selections from a Table

How can I select multiple data of a field from a Table on one screen. Then apply a filter to a form and show only those records selected.
Who is Participating?
Startrac98Connect With a Mentor Author Commented:
Thank you
Dale FyeCommented:
Generally, the only way to select multiple values from a single field and include those values in a filter is to use a listbox (or possibly a continuous form based upon a query that selects distinct values from the underlying table).

Assuming you have such a list, which has the Multi-select property set to either Simple or Expanded, I use a function to do it.  So, I would have a list (lst_FilterField) that has a row source that looks something like:

SELECT DISTINCT [FieldName] from yourTable

Then I would have a button (cmd_Filter) which contains the following code in the Click event:
Private Sub cmd_Filter_Click

    me.Filter = fnMultiList(me.lst_FilterField)
    me.filteron = Len(me.filter) > 0

End Sub

Open in new window

And the function looks like:
Public Function fnMultiList(lst As ListBox, Optional SelectAll As Boolean = False, _
                            Optional UseColumn As Integer = -1) As Variant

    Dim varItem As Variant
    Dim lngItem As Long
    Dim strDelimiter As String
    fnMultiList = Null
    'Determine how to delimit the list items
    If UseColumn = -1 Then UseColumn = lst.BoundColumn - 1
    If IsNumeric(lst.Column(UseColumn, Abs(lst.ColumnHeads))) Then
        strDelimiter = ""
        strDelimiter = Chr$(34)
    End If
    'loop through the selected items in the list
    If lst.MultiSelect = 0 And SelectAll = False Then
        fnMultiList = lst.Value
        For lngItem = 0 To lst.ListCount
            If lst.Selected(lngItem) = True Or SelectAll Then
                fnMultiList = (fnMultiList + ",") _
                            & strDelimiter & lst.Column(UseColumn, lngItem) _
                            & strDelimiter
            End If
        Next lngItem
    End If
    'Strip trailing "," if there is one
    If Right(fnMultiList, 1) = "," Then fnMultiList = Left(fnMultiList, Len(fnMultiList) - 1)
    'Depending on the number of items selected, determine how the "list values" are returned
    If Len(fnMultiList & "") = 0 Then
        fnMultiList = " IS NOT NULL"
        Select Case Len(fnMultiList) - Len(Replace(fnMultiList, ",", ""))
            Case 0
                fnMultiList = " = " & fnMultiList
            Case Else
                fnMultiList = " IN (" & fnMultiList & ")"
        End Select
    End If
End Function

Open in new window

Startrac98Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Startrac98's comment #a40710128

for the following reason:

solved coding problem
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Dale FyeCommented:
I think you may have selected the wrong solution.
Startrac98Author Commented:
Helped solved the problem.
Dale FyeCommented:

Did you intend to select your solution as the solution to your question, or did you intend to select my previous post?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.