Filter Report on multiple selections in a list box

If possible, use the WhereCondition of the OpenReport action instead of
filtering in the query.

This assumes you have a form that contains the listbox and textboxes used
for filtering. The form will have a command button to open the report. The
OpenReport action has a WhereCondition. Build up a string consisting of the
values you wish to filter by, and then use that as the WhereCondition of the
OpenReport action.

To create the string from the items in a multi-select list box, your code
will need to loop through the ItemsSelected collection of the list box. Use
ItemData. Remember to include the quote delimiters if the field being
filtered is of type Text (not Number). After concatenating the items
together, use the IN operator. The resultant string will be something like
this:
"[Surname] IN (""Jones"", ""Smith"", ""O'Brien"")"

For multiple conditions, include an AND in the WhereCondition string.

For an example of how to work with a start date and end date, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"arthur-e" <ar*****@ix.netcom.com> wrote in message
news:du********************************@4ax.com...

I can filter a query for a report on a field of a subform BUT now....
I'd like to be able to select more than one item in a multi-list box
to select all the records.

ALSO
to use two fields (or more) fields to filter a query based on
selections in two or more fields:

All the records with NAME = all the names selected in a list box
BETWEEN StartDate and EndDate

Thanks, Allen - yes, I'd like to use the WhereCondition not really a
filter - but I always have trouble with the syntax -
I thought the WhereCondition would be a concantenation with "OR"
between the selected items.

Do you know of an example somewhere on the web I could look at?

Arthur

"Allen Browne" <ab***************@bigpond.net.au> wrote:

If possible, use the WhereCondition of the OpenReport action instead of
filtering in the query.

This assumes you have a form that contains the listbox and textboxes used
for filtering. The form will have a command button to open the report. The
OpenReport action has a WhereCondition. Build up a string consisting of the
values you wish to filter by, and then use that as the WhereCondition of the
OpenReport action.

To create the string from the items in a multi-select list box, your code
will need to loop through the ItemsSelected collection of the list box. Use
ItemData. Remember to include the quote delimiters if the field being
filtered is of type Text (not Number). After concatenating the items
together, use the IN operator. The resultant string will be something like
this:
"[Surname] IN (""Jones"", ""Smith"", ""O'Brien"")"

For multiple conditions, include an AND in the WhereCondition string.

For an example of how to work with a start date and end date, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

-warning e-mail address altered- arthureNOSPACE@

If you use the OR operator, all the records that are named in the list box
will be included. Any records between the dates will also be included
(regardless of whether they match the listbox), since they only have to met
one condition or the other.

If you use the AND operator, only the records that are named in the list box
and also fall in the date range will be returned.

The basic idea for looping through the items of the list box is this:

Dim vItem As Variant
Dim lngLen as Long

With Me.MyListbox
For Each vItem In .ItemsSelected
If Not IsNull(vItem) Then
strWhere = strWhere & """" & .ItemData(vItem) & """, "
End If
Next
End With
lngLen = Len(strWhere) - 2 'Without trailing comma and space.
If lngLen > 0 Then
strWhere = "[MyField] IN (" & Left$(strWhere, lngLen) & ")"
End If
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"arthur-e" <ar*****@ix.netcom.com> wrote in message
news:7j********************************@4ax.com...

Thanks, Allen - yes, I'd like to use the WhereCondition not really a
filter - but I always have trouble with the syntax -
I thought the WhereCondition would be a concantenation with "OR"
between the selected items.

Do you know of an example somewhere on the web I could look at?

Arthur

"Allen Browne" <ab***************@bigpond.net.au> wrote:

If possible, use the WhereCondition of the OpenReport action instead of
filtering in the query.

This assumes you have a form that contains the listbox and textboxes used
for filtering. The form will have a command button to open the report. TheOpenReport action has a WhereCondition. Build up a string consisting of thevalues you wish to filter by, and then use that as the WhereCondition of theOpenReport action.

To create the string from the items in a multi-select list box, your code
will need to loop through the ItemsSelected collection of the list box. UseItemData. Remember to include the quote delimiters if the field being
filtered is of type Text (not Number). After concatenating the items
together, use the IN operator. The resultant string will be something likethis:
"[Surname] IN (""Jones"", ""Smith"", ""O'Brien"")"

For multiple conditions, include an AND in the WhereCondition string.

For an example of how to work with a start date and end date, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

-warning e-mail address altered- arthureNOSPACE@