Run Access Query based on List Box Select

I need to run a query where the criteria is one or more of the values in a list box.  How do I do that?
Select-Contract-Status.jpg
scs-contractsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Rey Obrero (Capricorn1)Commented:
if you are using A2007 or higher you can use the Tempvars collection as the criteria of the query.

what version of Access are you using?
Rey Obrero (Capricorn1)Commented:
or using VBA you can build a string for all items selected from the listbox and alter the querydef of the query.
scs-contractsAuthor Commented:
Access 2010
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rey Obrero (Capricorn1)Commented:
or using VBA you can build a string using a variable "strCriteria" for all items selected from the listbox, then alter the querydef of the query using the strCriteria as the criteria of the query
Dale FyeOwner, Developing Solutions LLCCommented:
I have a function which I use to come up with a list of the items selected in the listbox.  I then append that to a SQL String created at run time.  The function looks like:

Public Function fnMultiList(lst As ListBox, Optional SelectAll As Boolean = False, _
                            Optional UseColumn As Integer = -1, _
                            Optional Delimiter As Variant = Null) 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 IsNull(Delimiter) = False Then
        strDelimiter = Delimiter
    ElseIf 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

When I call this, I do something like:

strSQL = "SELECT * FROM MyTable WHERE [SomeField] " & fnMultiList(me.lst_name)

The function will return values like:

IS NOT NULL
= 3
= "Active"
IN ("Active", "Buy Out")

You might prefer to check the count of items selected in the list before calling the function, that way, you could bypass the:

WHERE [SomeField] IS NOT NULL

criteria if none of the items is selected.

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
scs-contractsAuthor Commented:
What does the 1st part of the code do "'Determine how to delimit the list items"
Dale FyeOwner, Developing Solutions LLCCommented:
Once you stick data in a listbox it is all interpreted as text, notice how even numeric fields are left justified.  So, what that code does is attempt to determine whether the bound column (or more accurately, the  column associated with the UseColumn argument) contains numbers.  If so, it sets the delimiter (the string to wrap around the actual value) to an empty string, so that numbers will not be surrounded in quotes.  If the value in that column is text, then it set strDelimiter = ".
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.