What I have is an Access report that is filtered by a multi-select list-box. So the end user is presented with the list box and then selects a button the opens the filtered report. But now the user wants and additional list-box filter called sSF or Square Feet. So now the query needs to have Categories and Square Feet.
Right now I was Here is my code. I need to add the additional filter to the DoCmd somehow?
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim sSF As String
Dim strWhere 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 Category"
'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)
' Square Feet field
Select Case Me.cboBigBox
Case "Normal Report"
sSF = "[SizeSmall]>=0"
Case "Less than 5,000 SF"
sSF = "[SizeSmall]<5000"
Case "5,000 to 10,000 SF"
sSF = "[SizeSmall]>=5000 AND [SizeSmall]<10000"
Case "10,000 to 20,000 SF"
sSF = "[SizeSmall]>=10000 AND [SizeSmall]<20000"
Case "20,000 and above"
sSF = "[SizeSmall]>=20000"
'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 & ")"