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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> 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: 159
Go to the top of the page

Posts in this topic

Custom Search

RSSSearch   Top   Lo-Fi    18th November 2018 - 03:53 AM