Filtering Queries using List Box

[alan_mitchell]

Hi all,

I have a query which shows staff members and their campaigns. The 'criteria' part of the query looks up the staff member selected in a report's list box and filters for that staff member's campaigns.

So for example, if the user selects 'Joe Bloggs' on the report' list box, the query will filter to only show campaigns that belong to Joe Bloggs'.

This works fine, but I want to be able to provide an option to NOT FILTER any staff members at all. So perhaps if the user ticks a check box 'show campaigns for all staff', the query will show ALL records.

Any idea how to do this?

Thanks,
Alan

 

[pbaldy]

Try this method:

http://www.mvps.org/access/queries/qry0001.htm

 

[Datagopher]

I don't think that you would need a separate check box if I'm understanding you correctly. You could just do something like this. This is assuming that you are using a multi-select on your list box.

dim var as Variant
dim int as Integer
For Each var In Me.MyListBox.ItemsSelected
int = int + 1
Next
'Perform evaluation
If int < 1 Then
'No filter is set on report
else
'Filter is on report
End If

 

[alan_mitchell]

Pbaldy - thanks it worked well, just what I was after.

Datagopher - that would have worked but my problem was more with how to set the filter rather than how to check if the list box is blank.

Cheers,
Alan