Filter report based on list box and combo box


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

Advertisements

M

Marshall Barton

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


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

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


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


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

Advertisements

M

Marshall Barton

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


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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top