How do I use multiple list selections from form in query criteria?

[beebop83]

I'm working on an old database for someone at work, and there's a report that they need to run and they need to specify a start date and end date, and then they will choose several departments and the report should display information for any entry between the start and end date that is also one of the selected departments. The dates aren't an issue, I can get that data from either a form, or by prompting for the start/end date. Getting the report to work when multiple departments are selected from a list however has been a huge pain. I've tried to use the IN command in my query and then reference the items selected from the list on the form IN (N'Forms![MultiDepartment_Lookup]![mySelection]') and that hasn't worked.



I've also tried to make a VBA module that took the data selected in the list and constructed an SQL query that was stored in a variable which was used by a function, and then i tried to use the function in my query parameter, and that hasn't worked either. Does anyone have an suggestions?

[beebop83]

Ok, I think I'm making progress. I've got the following command and I've debugged it so that I don't get any errors, but when I enter the information on the form and click the Ok button, the circle spins for a few seconds while it does some computing, but then the report I want to display doesn't actually show up. Anybody have any suggestions?

DoCmd.OpenReport "CaseNoSummaryListing", acViewPreview, , "[Location] IN ( " & StrWhere & ") AND [DateOfEvent] >= (" & start & ") AND ([DateOfEvent] >= (" & enddt & ")", acWindowNormal