Report SQL Where In Select

[misscrf]

I have a report, where the user can build the where statement using multi-select list boxes and a combo box, each to filter different fields. The combination of filters chosen create a where statement. The trick is that I need to follow some rules to make this report run correctly. I need to get the records where the main record source has the sub record in its records (where in select...)

I am not sure how to create this where in (select...where...) so that I can plug in the 3 list box filters and the combo filter. Normally I build a strSQL statement with an strCriteria for the where. I am having issues building the statement because of all of the joins in the SQL. I tried to make the SQL a query, and then plug in the strCriteria, but just not getting to the solution.

Wondering if anyone has ran into this type of need, and how they have handled it.

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

When the filtering is too complex for the WHERE CONDITION of the DoCmd.OpenReport method, I base my reports on saved queries and use DAO code to update the SQL property faq701-7433.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thanks, Duane. I'll give that a try.

misscrf

It is never too late to become what you could have been ~ George Eliot