OK, so I have a report that has fields called SizeSmall and SizeLarge. These are presented as Small-Large to demonstrate a range. The report is bound to a table. I also have a form that I need to use to filter the report. My problem is that the form is not bound to a table and uses list items in a multi-select list box that I typed in. So for instance My end user might want to view a report that shows properties in the 1,000 - 2000sf range but also 10-15,000 sf range. So the form is multi select. Here is the code I have that I used in another form but it's multi-Select list is bound.
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim sSF As String
Dim strWhere As String
Dim strFilter As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.lstCategories.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Size"
'add selected values to string
Set ctl = Me.lstCategories
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
'If Len(sSF) > 0 Then strFilter = "[CategoryID] " & strWhere & " AND " & sSF
'DoCmd.OpenReport "FilteredReport", acViewReport, , "tblCategories.CategoryID IN(" & strWhere & ")"
strFilter = "tblCategories.SizeSmall IN(" & strWhere & ")"
If Len(sSF) > 0 Then strFilter = strFilter
DoCmd.OpenReport "FilteredReport", acViewReport, , strFilter
I need to edit the string but am not sure how or what to change it to.