Report controlled by multiple multi select list boxes?

[kbrooks]

Everytime I think I'm getting somewhere I hit a dead end. I mainly accomplish the things I need by using macros but I'm thinking that won't work with something this complicated. I've used code but mostly with help, and I'm not sure how to do something like this.

I have a form called DesignOwnForm.
On it there are 2 multi select list boxes, ChoosePhysician and ChooseSurgery.
There are also 2 checkboxes, DetailCheck and ListCheck.
Then there are 2 text fields, StartDate and EndDate.

The end user will:
Select their surgeries (multiple)
Select their physicians (possibly multiple)
Enter a StartDate
Enter a EndDate
Check the box for either the detail report or the list report

I want all of this to control the report that it opens....either ReportA if they check the DetailCheck or ReportB if they check the ListCheck.

And of course pull the records based on the criteria in the list boxes and the Start/End Dates.

And to make it even more complicated...I want it to look at 3 different fields to see if they match what they select in the Surgeries box. Those fields are Operation1, Operation2, Operation3.

Is it possible? Thanks much in advance!

 

[gemma-the-husky]

design a query including the fields you are interested in.

in a query column, put a formula name, criteria for each critical field

eg for surgery

includesurgery(surgeryno)

and in the criteria put true

you then need to declare boolean functions in a module, to test the data

so that

function includesurgery(testsurgery as long) as boolean


end function

now within the function you can test whether this surgery is included in your list selection box, and trherefore whether to return true or false.

still tricky as referencing list boxes slections is not trivial

you may also find a lot of functions may slow the query a little