List Box Multiple Selection Against Query

[fionny]

Hi All,

I wasnt sure if this was a question for the forms forum or here but I went with queries, anyway Access noob ahoy! I need to pull on your expertise.

I have a form with 2 List Boxes:

reasonlist1

&



CellList

Both of these boxes are set to multiselect = extended.

The button beside points to run a query QRY_COMMENTS_6_Line_Data

Within that query I have set 2 fields to go check the listboxes and use the entry as criteria... with:

[Forms]![Commenting]![CellList]
&
[Forms]![Commenting]![reasonlist1]

Now I understand after some googling that this works fine for single selection fields but not for multiselect.

So my question to you folks is how do I get multiselect to work?

Thanks a million
Fionn

[rpeare]

in order for this to function the way you want you would have to modify the WHERE clause of your query every time you ran it because you'd have to populate it with actual values and not the reference to the form/object name.

You'd also have to provide for a lot of and/or statements.

So let's say your cell list was

A
B
C

and your reason list was

D
E
F

in your example if someone chose A from cells and E from reasons you'd be searching another field in your list for AE ([forms]![Commenting]![celllist] & [Forms]![commenting]![reasonlist] forms a concantenated string)

so let's say you chose from your multi select list box

A and B
and from your reason list
D and E

you'd have to cover 4 possibilities

AD
AE
BD
BE

In your search.

here's an example of cycling through items in a list box:

http://stackoverflow.com/questions/5...macro-for-each

you'd have to cycle through one of your list boxes and for each item in that list box you'd have to cycle through ALL of the selected item in your secondary list box and create your WHERE clause of your query around that.

[fionny]

Right sounds like a lot of work given my Reasons box has around 15 in its list and the cells has 7. I think I might make do with just the single selections for the moment for people.

[fionny]

Double Post! Ignore