Filter report based on list box and combo box

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] & "'"
--
doh!!!

brytank44 wrote:

>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)

--
Marsh
MVP [MS Access]