Filtering a query using a multi select list Box

[nagiese]

Good afternoon:



I have not used VBA and I am a novice at creating forms in Access so I'm having difficulty with a form that I'm trying to put together. I am trying to build a form to use to filter the data in a query. My issue is that I have two multi select list boxes where I need to be able to select all or some of the items in each of the boxes and then have the query run based on the criteria selected on the form.

I'm able to get the query to run if I limit the list box to only one selection, but if I enable the multi select to Extended and run the query it returns an empty record set.

I apologize, but I'm not as tech savy/smart as most and I've not used VBA so I'm not able to translate someone else’s code to suite my purposes.

I wanted to attached a copy of my database in order for you to see what I'm trying to accomplish. But I am unable to upload any files to this forum site, I keep getting an error message.


Thank you in advance for all of your help.

[ajetrumpet]

i assume you can't use a multi select with the query wizard. you might have to create a dynamic object in code.

for instance, put a button on the form to run the query and do something like:

on error resume next

docmd.DeleteObject acQuery, "qry"

dim intcurrentrow as integer

sql = "select * from table where "

For intCurrentRow = 0 To me.listbox.ListCount - 1
        If me.listbox.Selected(intCurrentRow) Then 
            sql = sql & "field = '" & me.listbox.column(0, intcurrentrow) & "' or "
        end if
next intcurrentrow

sql = left(sql, len(sql) - 4)

currentdb.createquerydef "qry", sql
currentdb.querydefs.refresh
currentdb.openquery "qry"