Filter report based on list box and combo box

  • Thread starter Thread starter brytank44
  • Start date Start date
B

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] & "'"
 
brytank44 said:
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] & "'"
Click to expand...


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


Marshall Barton said:
brytank44 said:
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] & "'"
Click to expand...


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)
Click to expand...
 
brytank44 said:
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) & ")"
Click to expand...


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

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