Want to avoid a long WHERE clause

[duluter]

Hello everyone.

I have a report that basically has one summary page per client. Originally, I just ran the report without filtering it, so all clients were included.

But now I would like the option of choosing to include all the clients or only selected clients in the report.

I built a form that has an All Clients listbox on the left and a Selected Clients listbox on the right that allows the user to select which clients they want to include.

In the report's Load event, I figure out which clients they selected on the form and build a concatenated list of "OR"s that I append to the WHERE clause of the record source for the report.

In other words,

sSelectedClients = "(Client = 'SelectedClient1') OR (Client = 'SelectedClient2') OR (Client = 'SelectedClient3')"

me.recordsource = "SELECT...FROM MyStoredQuery WHERE " & sSelectedClients


This works out pretty good except if the user selects a lot of clients to add to the report. When that happens, my string of ORs is too long and I get a Query is too complex error.


What would be a better way to implement this?


Thanks,

Duluter

 

[pbaldy]

Try

WHERE Client IN('SelectedClient1', 'SelectedClient2', 'SelectedClient3')

This may help:

http://www.baldyweb.com/multiselect.htm

 

[duluter]

Thanks, Paul.

I will try that.


Duluter