Filter with multiple option groups

[flux]

Hello everyone,

I hope this is the right place to post this question. I am currently creating a form which I filter while using multiple option groups but for the moment I can just filter individually and not multiple values simultaneously from the other option groups.

My code looks like that, so there are five individual option groups. So far I am thinking of an AND statement but since I am not advanced in VBA/SQL I struggle with the technical implementation.

Private Sub F_Verkauf_Filter_AfterUpdate()
'Form-Filter setzen
    Select Case F_Verkauf_Filter
      Case 1
        Me.Filter = "[FordV_Int]= 1"
        Me.FilterOn = True
       Case 2
        Me.Filter = "[FordV_Int]= 2"
        Me.FilterOn = True
       Case 3
        Me.Filter = "[FordV_Int]= 3"
        Me.FilterOn = True
    End Select
End Sub
 
Private Sub IHS_Filter_AfterUpdate()
'Form-Filter setzen
    Select Case IHS_Filter
      Case 1
        Me.Filter = "[IHS]= 1"
        Me.FilterOn = True
       Case 2
        Me.Filter = "[IHS]= 2"
        Me.FilterOn = True
    End Select
End Sub
 
Private Sub NZ_Filter_AfterUpdate()
'Form-Filter setzen
    Select Case NZ_Filter
      Case 1
        Me.Filter = "[Negativzinsen]= 1"
        Me.FilterOn = True
       Case 2
        Me.Filter = "[Negativzinsen]= 2"
        Me.FilterOn = True
       Case 3
        Me.Filter = "[Negativzinsen]= 3"
        Me.FilterOn = True
    End Select
End Sub
 
Private Sub Swap_Filter_AfterUpdate()
Select Case Swap_Filter
      Case 1
        Me.Filter = "[Zins_SWAP_JN]= 1"
        Me.FilterOn = True
       Case 2
        Me.Filter = "[Zins_SWAP_JN]= 2"
        Me.FilterOn = True
    End Select
End Sub
 
Private Sub Syn_Filter_AfterUpdate()
'Form-Filter setzen
    Select Case Syn_Filter
      Case 1
        Me.Filter = "[Syndizierung]= 1"
        Me.FilterOn = True
      Case 2
        Me.Filter = "[Syndizierung]= 2"
        Me.FilterOn = True
    End Select
End Sub
 
Private Sub TG_Filter_AfterUpdate()
'Form-Filter setzen
    Select Case TG_Filter
      Case 1
        Me.Filter = "[TGeld_JN]= 1"
        Me.FilterOn = True
       Case 2
        Me.Filter = "[TGeld_JN]= 2"
        Me.FilterOn = True
    End Select
End Sub

I hope someone can help me with my problem.

Best regards!

[Welshgasman]

I think you would need a button to sync all the likely filters?

Firstly from what you have posted you can just use

Me.Filter = "Field = " & OptionGroupName

I would create a function called by that button that will test each option group and build the filter string incrementally, set it and then set FilterOn to true?

[pbaldy]

Unless my eyes missed something, you don't need all the Case statements. I'd have a form level function and call it from each of the frames. You can concatenate them all together like:

Me.Filter = "[IHS] = " & Me.IHS_Filter & " AND FordV_Int = " & Me.F_Verkauf_Filter & " AND ...

[flux]

First of all thank you both so much!

So my new code looks like that. However, it does not run at all do I need to create a function or somewhat?

   Me.Filter = "[FordV_Int]= 1" & Me.F_Verkauf_Filter & " AND [IHS]= 1" & Me.IHS_Filter & " AND [Negativzinsen] = 1" & Me.NZ_Filter & " AND [Zins_SWAP_JN] = 1" & Me.Swap_Filter & " AND [Syndizierung] = 1" & Me.Syn_Filter & " AND [TGeld_JN] = 1" & Me.TG_Filter
        Me.FilterOn = True
       
        Me.Filter = "[FordV_Int]= 2" & Me.F_Verkauf_Filter & " AND [IHS]= 2" & Me.IHS_Filter & " AND [Negativzinsen] = 2" & Me.NZ_Filter & " AND [Zins_SWAP_JN] = 2" & Me.Swap_Filter & " AND [Syndizierung] = 2" & Me.Syn_Filter & " AND [TGeld_JN] = 2" & Me.TG_Filter
        Me.FilterOn = True
 
        Me.Filter = "[FordV_Int]= 3" & Me.F_Verkauf_Filter & " AND [Negativzinsen] = 3" & Me.NZ_Filter
        Me.FilterOn = True

[Welshgasman]

I would ask, are all the option groups going to be selected?
If not, you need to test each and only add to the filter string if not null?

You can only have ONE filter at a time, so they ALL have to be in the one filter string?

also you just want the likes of

"[FordV_Int]= " & Me.F_Verkauf_Filter

No idea why you have =1 ,=2, =3

[flux]

Not all the option groups will always be selected. So its possible that there are just 1,2,3... selected out of the total 5. How would the test look like?
I took the numbers from my previous code since those are the values of the booleans.

[Welshgasman]

So either test for Null, or use NZ() with 0 as the replacement and then test for > 0 to append "AND <fieldname> = " & <optionGroup> to the filter string.
Then when all that is set, set FilterOn

[pbaldy]

Most of us would build a string variable and then use it for the filter. Your test would look like:

If Not IsNull(Me.FrameName) Then
  strSQL = strSQL & "FieldName = " & Me.FrameName & " AND "
End If

Then at the end trim off the trailing " AND ":

strSQL = Left(strSQL, Len(strSQL) - 5)

[pbaldy]

Geez, slow again.

[Welshgasman]

Geez, slow again.

Good to know I was on the right track