Filtering a report from 2 listboxes (both refering to the same field)

[gvelix]

Hi!, I need some help please:

I have 4 listboxes that filter a report, 2 (ListboxPartner and ListboxPartnerN) of them are supposed to filter the same field (Partner) (I needed to split this field into this 2 listboxes for practical purpouses). Everything is working, but when I make a selection from this 2 listboxes (ListboxPartner and ListboxPartnerN) at the same time, the result is a blank report.

Here is my code:

Private Sub cmdApplyFilter_Click()
Dim varItem As Variant
Dim strCountry As String
Dim strPartner As String
Dim strPartnerN As String
Dim stDocName As String
Dim strYear As String
Dim strFilter As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rptQuarterly") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string from lstCountry listbox
For Each varItem In Me.lstCountry.ItemsSelected
strCountry = strCountry & ",'" & Me.lstCountry.ItemData(varItem) _
& "'"
Next varItem
If Len(strCountry) = 0 Then
strCountry = "Like '*'"
Else
strCountry = Right(strCountry, Len(strCountry) - 1)
strCountry = "IN(" & strCountry & ")"
End If
' Build criteria string from lstPartner listbox
For Each varItem In Me.lstPartner.ItemsSelected
strPartner = strPartner & ",'" & Me.lstPartner.ItemData(varItem) _
& "'"
Next varItem
If Len(strPartner) = 0 Then
strPartner = "Like '*'"
Else
strPartner = Right(strPartner, Len(strPartner) - 1)
strPartner = "IN(" & strPartner & ")"
End If

' Build criteria string from lstPartnerN listbox
For Each varItem In Me.lstPartnerN.ItemsSelected
strPartnerN = strPartnerN & ",'" & Me.lstPartnerN.ItemData(varItem) _
& "'"
Next varItem
If Len(strPartnerN) = 0 Then
strPartnerN = "Like '*'"
Else
strPartnerN = Right(strPartnerN, Len(strPartnerN) - 1)
strPartnerN = "IN(" & strPartnerN & ")"
End If

' Build criteria string from lstYear listbox
For Each varItem In Me.lstYear.ItemsSelected
strYear = strYear & "," & Me.lstYear.ItemData(varItem) _
& ""
Next varItem
If Len(strYear) = 0 Then
strYear = "Like '*'"
Else
strYear = Right(strYear, Len(strYear) - 1)
strYear = "IN(" & strYear & ")"
End If

' Build filter string
strFilter = "[Country] " & strCountry & _
" AND [Partner] " & strPartner & _
" AND [Partner] " & strPartnerN & _
" AND [Year] " & strYear

So basically the strPartner must be made from lstPartner and lstPartnerN, but try after try, it just doesn't work

Your help would be greatly appreciated!

 

[gvelix]

A little update (and an example)

Now the filter works only when I select at least one partner, one from each listbox. But if I select only one or more partners from one of the listboxes (leaving the other blank) the filter shows me all the possible partners. How can i correct this? I think that I have to modify the code to show no records from a certain field if no item was picked from that listbox.

I've just made an example of what I need (see attachment please )

Lets says that you want info for: Country: Antigua y Barbuda and Partner A: Tom : the results aren't right because it shows information for the all the partners

But if you chose 2 partners, let's say Tom from A and Harry from B it works. So it's working in part.

Would you help me please??

Your help is much appreciated!

 

[jdraw]

A little update (and an example)

Now the filter works only when I select at least one partner, one from each listbox. But if I select only one or more partners from one of the listboxes (leaving the other blank) the filter shows me all the possible partners. How can i correct this? I think that I have to modify the code to show no records from a certain field if no item was picked from that listbox.

I've just made an example of what I need (see attachment please )

Lets says that you want info for: Country: Antigua y Barbuda and Partner A: Tom : the results aren't right because it shows information for the all the partners

But if you chose 2 partners, let's say Tom from A and Harry from B it works. So it's working in part.

Would you help me please??

Your help is much appreciated!

When only 1 combobox is selected (I selected the top comboBox - Partner A), your strfilterN say Partner like "*", which is interpreted as 'Any Partner'.
So the report is set to use Any Partner (all values).

I think you have to build the 'Or ' part of your filter as you determine if there is a selected item(s) in the combobox.