B
Brian
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.
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.