multiple filters in multiple fields

D

dedjloco

Registered User.
Local time
Today, 22:58
Joined
Mar 2, 2017
Messages
49
The title is maybe a little vague.
What I'm trying to do is creating a macro to filter data in a subform.
But what I'm trying I can't find anywhere.
First I want multiple filters on 1 field. So when there are multiple statements true I get all the records that meet the statements.
This works fine on itself.
But after this I want that kind of filter on multiple fields, but with different statements. So when multiple statements on 1 filter and multiple statements on an other filter are true I get all the records of all the statements.
I wrote the following code, but it seems to go wrong with combining the two filters. Because it does filter the first statement but doen't wilter the second.

Code: 
Public Sub FilterMacro()
Dim FilterString As String
Dim ProductFilter As String
Dim DriveFilter As String
Dim FeaturesFilter As String
Dim SWLFilter As String
Dim BoomFilter As String
Dim ProductLen As Long
Dim DriveLen As Long
Dim ingLen As Long
 
If Me.BH_Check = True Then
    ProductFilter = ProductFilter & "([Product type] = 'BH') OR "
End If
If Me.GW_Check = True Then
    ProductFilter = ProductFilter & "([Product type] = 'GW') OR "
End If
If Me.KB_Check = True Then
    ProductFilter = ProductFilter & "([Product type] = 'KB') OR "
End If
If Me.RL_Check = True Then
    ProductFilter = ProductFilter & "([Product type] = 'RL') OR "
End If
If Me.Other_Check = True Then
    ProductFilter = ProductFilter & "([Product type] = 'Other') OR "
End If
ProductLen = Len(ProductFilter) - 4
If Not ProductLen <= 0 Then
    ProductFilter = Left$(ProductFilter, ProductLen)
End If
If Me.DHC_Check = True Then
    DriveFilter = DriveFilter & "([Drive type] = 'DHC') OR "
End If
If Me.EHC_Check = True Then
    DriveFilter = DriveFilter & "([Drive type] = 'EHC') OR "
End If
If Me.EC_Check = True Then
    DriveFilter = DriveFilter & "([Drive type] = 'EC') OR "
End If
If Me.NotSpecified_Check = True Then
    DriveFilter = DriveFilter & "([Drive type] = 'Open') OR "
End If
If Me.Open_Check = True Then
    DriveFilter = DriveFilter & "([Drive type] = 'Not Specified') OR "
End If
DriveLen = Len(DriveFilter) - 4
If Not DriveLen <= 0 Then
    DriveFilter = Left$(DriveFilter, DriveLen)
End If
 
FilterString = ProductFilter & DriveFilter
    
lngLen = Len(FilterString)
If Not lngLen <= 0 Then
    Me.Competitors_subform.Form.Filter = FilterString
    Me.Competitors_subform.Form.FilterOn = True
End If
 
If Me.BH_Check = False And Me.GW_Check = False And Me.KB_Check = False And Me.RL_Check = False And Me.Other_Check = False Then
    Me.Competitors_subform.Form.FilterOn = False
End If
    
End Sub

PS this code should get more filters when it works. But that should be just copy and paste.
 
Depending on what you are trying to do I think you want this line:
Code: 
FilterString = ProductFilter & DriveFilter

to be

Code: 
FilterString = ProductFilter & " And " &  DriveFilter

or
Code: 
FilterString = ProductFilter & " Or " & DriveFilter
 
Thank you, it did what I hoped.
I changed it a little as you can see below, because it doesn't work when you don't check a box in the drivetype category, because then it comes up with "ProductFilter AND". But this was solved in my code.
But it brings up another problem. Now if you check multiple boxes in the producttype category first and then check a box in the drivetype category it only filters the last filter that was applied to the producttype category.
So I think the outcome is something like:
Code: 
([Product type] = 'BH') OR ( ([Product type] = 'GW') AND ([Drive type] = 'DHC') )
instead of:
Code: 
( ([Product type] = 'BH') OR ([Product type] = 'GW') ) AND ([Drive type] = 'DHC')
note the difference in parentheses
So I get all the records with BH in it, but only the records with GW and DHC in it. And it should give only the records with DHC in it, from the records with BH or GW in it.

Code: 
'Filter Competitors

Public Sub FilterMacro()
Dim FilterString As String
Dim ProductFilter As String
Dim DriveFilter As String
Dim FeaturesFilter As String
Dim SWLFilter As String
Dim BoomFilter As String
Dim ProductLen As Long
Dim DriveLen As Long
Dim ingLen As Long
If Me.BH_Check = True Then
    ProductFilter = ProductFilter & "([Product type] = 'BH') OR "
End If
If Me.GW_Check = True Then
    ProductFilter = ProductFilter & "([Product type] = 'GW') OR "
End If
If Me.KB_Check = True Then
    ProductFilter = ProductFilter & "([Product type] = 'KB') OR "
End If
If Me.RL_Check = True Then
    ProductFilter = ProductFilter & "([Product type] = 'RL') OR "
End If
If Me.Other_Check = True Then
    ProductFilter = ProductFilter & "([Product type] = 'Other') OR "
End If
ProductLen = Len(ProductFilter) - 4
If Not ProductLen <= 0 Then
    ProductFilter = (Left$(ProductFilter, ProductLen)) & " AND "
End If
If Me.DHC_Check = True Then
    DriveFilter = DriveFilter & "([Drive type] = 'DHC') OR "
End If
If Me.EHC_Check = True Then
    DriveFilter = DriveFilter & "([Drive type] = 'EHC') OR "
End If
If Me.EC_Check = True Then
    DriveFilter = DriveFilter & "([Drive type] = 'EC') OR "
End If
If Me.NotSpecified_Check = True Then
    DriveFilter = DriveFilter & "([Drive type] = 'Open') OR "
End If
If Me.Open_Check = True Then
    DriveFilter = DriveFilter & "([Drive type] = 'Not Specified') OR "
End If
DriveLen = Len(DriveFilter) - 4
If Not DriveLen <= 0 Then
    DriveFilter = (Left$(DriveFilter, DriveLen)) & " AND "
End If
FilterString = (ProductFilter) & (DriveFilter)
lngLen = Len(FilterString) - 5
If Not lngLen <= 0 Then
    FilterString = Left$(FilterString, lngLen)
    Me.Competitors_subform.Form.Filter = FilterString
    Me.Competitors_subform.Form.FilterOn = True
End If
If Me.BH_Check = False And Me.GW_Check = False And Me.KB_Check = False And Me.RL_Check = False And Me.Other_Check = False Then
    Me.Competitors_subform.Form.FilterOn = False
    FilterString = 0
End If
    
End Sub
 
So why not just try:

Code: 
FilterString =  "(" & ProductFilter & ") And (" &  DriveFilter & ")"
 
I think that needs some more explanation, because it doesn't work at all when I put it in. even when I delete the AND's and the part that gets rid of the extra AND at the end.
 
I didn't look close enough at the code you posted in post 3. I don't understand why you added

Code: 
If Not DriveLen <= 0 Then
    DriveFilter = (Left$(DriveFilter, DriveLen)) & " AND "
End If

and
Code: 
lngLen = Len(FilterString) - 5


If ProductFilter is a string like "A or B or C" and DriveFilter is a string like "D or F"

then

Code: 
FilterString =  "(" & ProductFilter & ") And (" &  DriveFilter & ")"

would product a filter string equal to

( A or B or C) And (D or F)

without these additions
 
I put in this:
Code: 
If Not DriveLen <= 0 Then
DriveFilter = (Left$(DriveFilter, DriveLen)) & " AND "
End If
lngLen = Len(FilterString) - 5
Because when you do it your way it doesn't work when you don't put anything in the second filter. because it doesn't understand the AND.
I could solve that en other way, but this is more elegant when I would add more filters like these.
But I tried your code but it didn't work. I left out the stuff above, but no success.
Any idea why? maybe you could explain what your string of code does exactly?
 
I'm sorry it does work, but still only when both filters are active. Do you know a way to make it work with 1 filter active. If you know what I mean?
and when I add more filters you would get someting like this:
FilterString = "(" & ProductFilter & ") And (" & DriveFilter & ")" And (" & ExtraFilter & ")"
but when you wouldn't put on the "DriveFilter", it won't work either because it would see 2 AND's after each other.
 
Last edited:
oke I messed around for a little and got the solution. and it works like a charm when you add more filters
Here is the code:
Code: 
'Filter Competitors

Public Sub FilterMacro()
Dim FilterString As String
Dim ProductFilter As String
Dim DriveFilter As String
Dim FeaturesFilter As String
Dim SWLFilter As String
Dim BoomFilter As String
Dim ProductLen As Long
Dim DriveLen As Long
Dim FeaturesLen As Long
Dim SWLLen As Long
Dim BoomLen As Long
Dim ingLen As Long
If Me.BH_Check = True Then
    ProductFilter = ProductFilter & "([Product type] = 'BH') OR "
End If
If Me.GW_Check = True Then
    ProductFilter = ProductFilter & "([Product type] = 'GW') OR "
End If
If Me.KB_Check = True Then
    ProductFilter = ProductFilter & "([Product type] = 'KB') OR "
End If
If Me.RL_Check = True Then
    ProductFilter = ProductFilter & "([Product type] = 'RL') OR "
End If
If Me.Other_Check = True Then
    ProductFilter = ProductFilter & "([Product type] = 'Other') OR "
End If
ProductLen = Len(ProductFilter) - 4
If Not ProductLen <= 0 Then
    ProductFilter = Left$(ProductFilter, ProductLen)
    ProductFilter = "(" & ProductFilter & ")" & " AND "
End If
If Me.DHC_Check = True Then
    DriveFilter = DriveFilter & "([Drive type] = 'DHC') OR "
End If
If Me.EHC_Check = True Then
    DriveFilter = DriveFilter & "([Drive type] = 'EHC') OR "
End If
If Me.EC_Check = True Then
    DriveFilter = DriveFilter & "([Drive type] = 'EC') OR "
End If
If Me.NotSpecified_Check = True Then
    DriveFilter = DriveFilter & "([Drive type] = 'Not Specified') OR "
End If
If Me.Open_Check = True Then
    DriveFilter = DriveFilter & "([Drive type] = 'Open') OR "
End If
DriveLen = Len(DriveFilter) - 4
If Not DriveLen <= 0 Then
    DriveFilter = Left$(DriveFilter, DriveLen)
    DriveFilter = "(" & DriveFilter & ")" & " AND "
End If
If Me.AMC_Check = True Then
    FeaturesFilter = FeaturesFilter & "([Features] = 'AMC') OR "
End If
If Me.AHC_Check = True Then
    FeaturesFilter = FeaturesFilter & "([Features] = 'AHC') OR "
End If
If Me.Telescopic_Check = True Then
    FeaturesFilter = FeaturesFilter & "([Features] = 'Telescopic') OR "
End If
If Me.Manriding_Check = True Then
    FeaturesFilter = FeaturesFilter & "([Features] = 'Manriding') OR "
End If
FeaturesLen = Len(FeaturesFilter) - 4
If Not FeaturesLen <= 0 Then
    FeaturesFilter = Left$(FeaturesFilter, FeaturesLen)
    FeaturesFilter = "(" & FeaturesFilter & ")" & " AND "
End If
FilterString = ProductFilter & DriveFilter & FeaturesFilter
lngLen = Len(FilterString) - 5
If Not lngLen <= 0 Then
    FilterString = Left$(FilterString, lngLen)
    Me.Competitors_subform.Form.Filter = FilterString
    Me.Competitors_subform.Form.FilterOn = True
End If
If Me.BH_Check = False And Me.GW_Check = False And Me.KB_Check = False And Me.RL_Check = False And Me.Other_Check = False Then
    Me.Competitors_subform.Form.FilterOn = False
End If
    
End Sub
 
oke postes something but the solution was somewhere else
 
Last edited:
You must log in or register to reply here.

Similar threads

C
Invalid use of Me.
Replies
8
Views
1,043
cheekybuddha
cheekybuddha
C
Field has no value
Replies
19
Views
982
The_Doc_Man
The_Doc_Man
N
How to send filtered transactions in the MS Access Combo box with one Click
Replies
15
Views
904
Mark_
M
F
Solved Form with subform filtered by two cascading combo boxes and a checkbox
2
Replies
26
Views
1,864
Faoineag
F
N
Solved How to update bulk selling prices at one go
Replies
5
Views
368
nector
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom