X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Using Multi-select List Boxes In Queries, Access 2007    
post Oct 16 2015, 09:29 PM

Posts: 2,428
Joined: 12-February 15
From: SW AZ

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.
  • ListBoxKey is any key contained in a ListBoxes collection (more later). (Trying to use Form!YourForm!YourListBox proves to be too complex for Access. We thus need to simply the expression, if not the process.)
  • Value is any value to test as being selected in the ListBox. (This would typically be the field whose value you want tested.)
  • (Optional) NoneSelected =
    • True (default) causes True to be returned if no items are selected in the list. (This is a preference of many programmer: If nothing is selected, then select all.)
    • False cause the function to return True only if the item is selected and it's itemdata = Value.

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
  • ListBoxes.Add ListBox, ListBoxKey to add all your listboxes in the Form's On Load event.
  • ListBoxes.Remove ListBoxKey to remove all your listboxes in the Form's UnLoad event.

To use the ValueInList function, add it to your criteria (SQL is easiest to describe):
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.
Attached File  MultiListBoxQueries.zip ( 28.33K )Number of downloads: 154
Go to the top of the page
post Oct 17 2015, 06:32 AM

Posts: 2,428
Joined: 12-February 15
From: SW AZ

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 Set query property using VBA.

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.
Attached File  MultiListBoxQueries03.zip ( 30.82K )Number of downloads: 207
Go to the top of the page
post Oct 17 2015, 09:26 AM

UA Admin
Posts: 33,767
Joined: 20-June 02
From: Newcastle, WA

Leigh Purvis is brilliant. He is an Access MVP and a good guy to boot.
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    19th October 2018 - 02:11 AM