I have a form with a multi value list box and what I want is for the users to select multiple cities they want, click on a command button then a report opens up the selected cities data shown in the report. I'm having a terrible time with this. I have the list box row source set to SELECT distinct [district] FROM districtqry order by district asc;
This works fine and this list box is populated with districts and when I click to run the report I get records of all districts not the selected districts.
districtqry is my query and is the record source for my district report, and all the district data is in my "conference" table which has a district column. I'm having trouble writing my SQL query, am I suppose to use the IN operator? here's what I have for my where clause
Dim strSQL As String
Dim varItem As Variant
For Each varItem In Me.List0.ItemsSelected
If Len(strSQL) > 0 Then strSQL = strSQL & ", "
strSQL = strSQL & "'" & Me.List0.ItemData(varItem) & "'"
strSQL = " WHERE District IN ( " & strSQL & " ) "
One way would be to declare strWhere as a public variable in a standard module. That lets you access it from other modules, so in your report's open event you can pop it into a textbox. Depending on version you could also pass it in OpenArgs and grab it from there in the open event. That would probably be my first option, but only available starting in 2002 if memory serves.