Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Code Archive _ Using Multi-select List Boxes In Queries

Posted by: azolder Oct 16 2015, 09:29 PM

On occasion it is necessary to use multi-select list boxes in queries. This topic presents one process to accomplish this.

The key to the process is the ValueInList(ListBoxKey, Value [, NoneSelected=True]) function.


Prior to using the ValueInList function, you need to add the listbox to the ListBoxes Collection
ListBoxes.Add ListBox, ListBoxKey

Use ListBoxKey in the ValueInList function to refer to the ListBox. You can have multiple ListBoxes in the ListBoxes Collection, enabling you to use the function multiple times, even in the same query. It is recommended that you use
To use the ValueInList function, add it to your criteria (SQL is easiest to describe):
SQL
SELECT *, ValueInList(Key1, [Field1]) AS Expr1, ValueInList(Key2,[Field2], False)AS Expr2, ... FROM [YourTable] WHERE (ValueInList(Key1,[Field1]))<>False AND (ValueInList(Key2,[Field2], False)<> False) ...;
The attachment contains the code as well as an example table, query and form using the function.
 MultiListBoxQueries.zip ( 28.33K ): 136

Posted by: azolder Oct 17 2015, 06:32 AM

Like they say, there's more than one way to do anything in Access. After a bit of time I wondered, "Is there a filter property on queries?"

The answer is yes. In fact Mr (Ms?) LPurvis solved this problem way back in 2008 with http://www.UtterAccess.com/forum/Set-query-property-VBA-t1713084.html.

The attachment just modifies my previous effort using his code. Note that Filter and FilterOnLoad need to be set. I also modified a UtterAccess function to evaluate both List Boxes.
 MultiListBoxQueries03.zip ( 30.82K ): 168

Posted by: GroverParkGeorge Oct 17 2015, 09:26 AM

Leigh Purvis is brilliant. He is an https://mvp.microsoft.com/en-us/PublicProfile/4000577?fullName=Leigh%20Purvis and a good guy to boot.