Filter report based on list box and combo box

[brytank44]

I have been trying to filter repotr based on a list box and combo box. I can
filter the report using either the combo box or list box but I'm stuck in
combining both. I get a type mismatch error message.

Here is the code I'm using.

Listbox is called lstAppealType
Combo box is called =cboHP (format is text)

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'add selected values to string
Set ctl = Forms!frmReports!lstAppealType
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)


'open the report, restricted to the selected items

DoCmd.OpenReport "rptAppealCntbyTypeMonthly", acPreview, , "AppealTypeID
IN(" & strWhere & ")" And "([HP]='" & [Forms]![frmReports]![cboHP] & "'"

 

[Marshall Barton]

I have been trying to filter repotr based on a list box and combo box. I can
filter the report using either the combo box or list box but I'm stuck in
combining both. I get a type mismatch error message.

Here is the code I'm using.

Listbox is called lstAppealType
Combo box is called =cboHP (format is text)

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'add selected values to string
Set ctl = Forms!frmReports!lstAppealType
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)


'open the report, restricted to the selected items

DoCmd.OpenReport "rptAppealCntbyTypeMonthly", acPreview, , "AppealTypeID
IN(" & strWhere & ")" And "([HP]='" & [Forms]![frmReports]![cboHP] & "'"

With Forms!frmReports
For Each varItem In !lstAppealType.ItemsSelected
strList = strList & "," _
& !lstAppealType.ItemData(varItem)
Next varItem
strWhere = strWhere & " AND " & AppealTypeID IN(" &
Mid(strList, 2) & ")"

If Not IsNull(!cboHP) Then
strWhere = strWhere & " AND HP='" & !cboHP & "'"
End If
End With

'open the report, restricted to the selected items
DoCmd.OpenReport "rptAppealCntbyTypeMonthly", _
acPreview, , Mid(strWhere , 6)

 

[brytank44]

Thank you. However, it doesn't like the IN the string below. Is it different
if the ApealType Id is a number(long integer)?

strWhere = strWhere & " AND " & AppealTypeID IN(" & Mid(strList, 2) & ")"
--
doh!!!

I have been trying to filter repotr based on a list box and combo box. I can
filter the report using either the combo box or list box but I'm stuck in
combining both. I get a type mismatch error message.

Here is the code I'm using.

Listbox is called lstAppealType
Combo box is called =cboHP (format is text)

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'add selected values to string
Set ctl = Forms!frmReports!lstAppealType
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)


'open the report, restricted to the selected items

DoCmd.OpenReport "rptAppealCntbyTypeMonthly", acPreview, , "AppealTypeID
IN(" & strWhere & ")" And "([HP]='" & [Forms]![frmReports]![cboHP] & "'"

With Forms!frmReports
For Each varItem In !lstAppealType.ItemsSelected
strList = strList & "," _
& !lstAppealType.ItemData(varItem)
Next varItem
strWhere = strWhere & " AND " & AppealTypeID IN(" &
Mid(strList, 2) & ")"

If Not IsNull(!cboHP) Then
strWhere = strWhere & " AND HP='" & !cboHP & "'"
End If
End With

'open the report, restricted to the selected items
DoCmd.OpenReport "rptAppealCntbyTypeMonthly", _
acPreview, , Mid(strWhere , 6)

 

[Marshall Barton]

Thank you. However, it doesn't like the IN the string below. Is it different
if the ApealType Id is a number(long integer)?

strWhere = strWhere & " AND " & AppealTypeID IN(" & Mid(strList, 2) & ")"

Sorry, there's an extra quote in there, It should be:

strWhere = strWhere & " AND AppealTypeID IN(" & Mid(strList,
2) & ")"