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.
Startrac98Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 = ""
    Else
        strDelimiter = Chr$(34)
    End If
    
    'loop through the selected items in the list
    If lst.MultiSelect = 0 And SelectAll = False Then
        fnMultiList = lst.Value
    Else
        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"
    Else
        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

0
Startrac98Author Commented:
Thank you
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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

Did you intend to select your solution as the solution to your question, or did you intend to select my previous post?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.