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

How to use Selected Items from ListBox as query criteria by MakeItSo
Posted: 14 Nov 03

You have a multi-select ListBox on a form and you want to get all Fields from a table or query that match your Selection in the listbox.


If you want to initiate the field search with e.g. a Command button, adapt the following code to your needs:

Sub YourSearchButton_Click()

Dim ctlList
Dim sSql as String

Set ctlList = Me.YourListbox
sSQL="SELECT * FROM YourTable Where "

For Each Lmnt In ctlList.itemsSelected
    sSQL=sSQL & "Surname= '" & ctlList.Itemdata(Lmnt) & "' OR "

sSQL=Left(sSQL,Len(sSQL)-3) 'Remove last 'OR' with space

Variation of this code is:

sSQL="SELECT * FROM YourTable WHERE Surname IN ('"

For Each Lmnt In ctlList.itemsSelected
    sSQL=sSQL & ctlList.Itemdata(Lmnt) & "', '"

sSQL=Left(sSQL,Len(sSQL)-3) & ")" 'Remove Last comma and single quote and add closing bracket

This code is of course valid for UPDATE, INSERT INTO or DELETE statements too, and only needs to be adapted respectively.

For those who need more complex statements:
Create a new query in query designer, switch to SQL view, copy/paste the statement into your code, make adaptions (esp. you might want to remove Forms![YourForm]! and set " & me!Whatever & "), where necessary.

Special thanks to PeteJohnston for the code variation.


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