query based on one or the other listbox

  • Thread starter Thread starter bizee
  • Start date Start date
B

bizee

I have a list of reports that can be run from my database. This is on
a form. The reports run all the data.

I have since expanded my ideas for this database to have a choice,
Run the reports based on the project manager assigned. OR by the
Project.

So Run report A (labor report) for all costs associated to John Doe.
Or
Run report A (labor report) for all costs associated with ProjectX

I have queries built to do either one. I couldn't figure out a way
to do an either/or.

The other option I have is to have the listbox and then the complete
list of reports below it to choose, and then have the listbox for the
subprojects with the list of reports below it. That would clutter the
form, so wasn't my first choice.

I'm thinking I cannot do this.
 
The most flexible way to interface this will be to leave the criteria out of
the report's queries. Instead, filter it with the WhereCondition of
OpenReport.

You already have a form that you fire the report from, and this form has
controls to select an employee and/or a project. You probably have a command
button to open the report as well. In the Click event procedure of this
command button, build the filter string from which ever boxes the user
filled in, so:
a) If they chose an employee, you filter to that employee.
b) If they chose a project, you filter to that project.
c) If they chose both, you filter to both.
d) If they chose neither, you show all (no filter.)

You will want to design this so it's easy to add more filter options for the
report as well. For an example of how to build such a filter string, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The sample database in that article applies its filter to the form, but the
process is exactly the same to build a filter for your report.
 
Back
Top