Passing list box results to query criteria

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Brian (Brian_at_discussions.microsoft.com)
Date: 02/14/05 

Date: Mon, 14 Feb 2005 07:55:01 -0800

I often use multiple combo boxes in conjuntion with the query builder to
allow users many "AND" filter options when running reports. I would like to
start using list boxes to allow multiple selections, but cannot figure out
how to get the list box contents to the field & criteria boxes in the query
builder.

Simplified example:

Form Name: Form1
It has about 10 combo boxes; each will allow a filter of a specific field.
The one I want to switch to a List Box is called Status; choices are Open;
Locked; Closed.

With a combo box, I would simply place these in a query field in the query
builder:

[Forms]![Form1]![Status]
First criteria line: Is Null
Second criteria line: Is Not Null

This would be in another query field:

Status (name of a field in the table against which the query/report is being
run)
First criteria line: blank
Second criteria line: [Forms]![Form1]![Status]

This ensures that the Status filter is applied only if the Status control on
the form is populated.

In attempting to switch to a list box, I got as far as looping through the
Status.Selected to extract & concatenate the Status.ItemData entries into an
"A Or B or C" (etc) string, but I have not yet successfully constructed the
string so that it can be passed to the second criteria line (above) for the
Status field in the query builder.

The examples I have seen previously all involve construction of the entire
Select statement in VBA, but given my other 9 combo (soon to be list) boxes,
that is looking exponentially more complex.

Relevant Pages

  • RE: Filtering a subform using many combo boxes
    ... SELECT AssignedPriority, AssignedPriorityDetail FROM tblAssignedPriority ... YES for all of the combo boxes.) ... I followed you instructions to the letter and every one of the criteria ... open the query "qryProjects" in design view. ...
    (microsoft.public.access.forms)
  • RE: Filtering a subform using many combo boxes
    ... I followed you instructions to the letter and every one of the criteria ... I described the typical properties of all my combo boxes in my original ... open the query "qryProjects" in design view. ... I have a main table named tblProjectData that contains all the data for each ...
    (microsoft.public.access.forms)
  • Re: Passing query parameter from form
    ... I built the code that specifies the "WHERE" criteria for the form filter, ... This becomes quite messy where you have lots of text boxes, ... criteria from the query, and build a filter string instead. ... The form has two text boxes: one for the Special Event, ...
    (microsoft.public.access.forms)
  • Re: Help With Problems Creating Listboxes In A Form
    ... I would like to use List boxes if possible - Meaning, ... criteria for all 4 categories or maybe just 2. ... I would like 4 listboxes that all have input into the query. ...
    (microsoft.public.access.forms)
  • Re: Passing query parameter from form
    ... You can have the query read the values from the text boxes on the form. ... This becomes quite messy where you have lots of text boxes, and some may be left blank. ... to handle that kind of situation, it may be better to omit the criteria from the query, and build a filter string instead. ... Salaries and Other Costs (again using Special Event and Department as search ...
    (microsoft.public.access.forms)