Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Queries and JET SQL FAQ

Using list boxes for Criteria

Use multiselect listbox without temp tables or SQL updates by dhookom
Posted: 9 Nov 05 (Edited 3 Mar 09)

You can create a function in a standard module as noted below. This function can then be used in a query to filter out values not selected in the list box. If no items are selected, the query will return all records.

The way you might implement this with the Employee table in Northwind with a form "frmMultiselectList" and a listbox "lboEmployeeID" uses this SQL:


SELECT Employees.*
FROM Employees
WHERE IsSelectedVar("frmMultiselectList","lboEmployeeID",[EmployeeID])=-1;

This function may not perform very well with very large tables.


Function IsSelectedVar( _
        strFormName As String, _
        strListBoxName As String, _
        varValue As Variant) _
            As Boolean
    'strFormName is the name of the form
    'strListBoxName is the name of the listbox
    'varValue is the field to check against the listbox
    Dim lbo As ListBox
    Dim item As Variant
    If IsNumeric(varValue) Then
        varValue = Trim(Str(varValue))
    End If
    Set lbo = Forms(strFormName)(strListBoxName)
    If lbo.ItemsSelected.Count = 0 Then
        IsSelectedVar = True 'return all if no items selected
        For Each item In lbo.ItemsSelected
            If lbo.ItemData(item) = varValue Then
                IsSelectedVar = True
                Exit Function
            End If
    End If
End Function

There is a sample MDB of this at http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Back to Microsoft: Access Queries and JET SQL FAQ Index
Back to Microsoft: Access Queries and JET SQL Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close