Filter records using multiple Option Groups 1

[bud31047]

How would I go about filtering records on a form using more than one option group? My goal is to combine the two filters together and not override the filter applied by option group1 when I make a selection in option group2.

Would this require an IIF statement inside the select/case or is there easier approach?

Thanks in advance,

 

[TheAceMan1]

How are ya bud31047 . . .

The problem with option groups is that [blue]they return an index against the selection made[/blue]. This index starts at one and increments by 1 for each option (usually top to bottom or left to right, depending how you physically have them set up). With this index, [blue]its up to you as the programmer to prescribe correctly, in code, what the indexes represent![/blue]

Example: Say we have the following two option groups:

[tt][blue]Idx Opt1 Opt2
*** **** *****
1 CA Men
2 NY Women
3 TX Children
4 Pets[/blue][/tt]

In code we pull out their selections like so (this is just one method, there are others depending what your doing):

[blue]   Dim selOpt1 As String, selOpt2 As String
   
   selOpt1 = Choose(Me!Opt1, "CA", "NY", "TX")
   selOpt2 = Choose(Me!Opt2, "Men", "Women", "Children", "Pets")[/blue]

Now that we have our selections, we can go ahead and setup the filter:

[blue]   Dim selOpt1 As String, selOpt2 As String, Pack As String
   
   selOpt1 = Choose(Me!Opt1, "CA", "NY", "TX")
   selOpt2 = Choose(Me!Opt2, "Men", "Women", "Children", "Pets")
   
   Pack = "[[purple][B][I]FieldName1[/I][/B][/purple]] = '" & selOpt1 & "' AND " & _
          "[[purple][B][I]FieldName2[/I][/B][/purple]] = '" & selOpt2 & "'"
   
   Me.Filter = Pack
   Me.FilterOn = True[/blue]

[blue]Your Thoughts? . . .[/blue]

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and gives insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[bud31047]

First off thanks for the reply.

Im not sure that the method you propose would be the best option for my particular situation. I am using and/or statments which may cause problems; maybe not. Also , I actually have 4 option groups on my form. The first option group applies a sort to a chosen field and the other three I am using to select specific data. Here is my code for the four filters. Maybe this would allow you and the other more experienced programmers to propose the best possible solution for my situation.

Private Sub SortOption_AfterUpdate()

'Variable to hold filtered SQL string
Dim strSortOptionSQL As String

'Set default record source of form
Const strSQL1 = "SELECT * from DetailQry"

    Select Case Me!SortOption
    'Filter record source dependant on option checked
        Case 1
            strSortOptionSQL = strSQL1 & " where [Doornum] is not null" & " Order By [Doornum]"
        Case 2
            strSortOptionSQL = strSQL1 & " where [TrailerID]> ''" & " Order By [TrailerID]"
        Case 3
            strSortOptionSQL = strSQL1 & " Order By [TripNumber]"
        Case 4
            strSortOptionSQL = strSQL1 & " Order By [DispatchDateTime]"
        Case 5
            strSortOptionSQL = strSQL1 & " where [status] is not null" & " Order By [Status]"

    'If filter applied with no option selected use default record source
        Case Else
            strSortOptionSQL = strSQL1 & ";"
    End Select
    
'Set record source with filtered SQL
    Me.RecordSource = strSortOptionSQL
    Me.Requery
End Sub

Private Sub LoadFilter_AfterUpdate()

'Variable to hold filtered SQL string
Dim strLoadFilterSQL As String

'Set default record source of form
Const strSQL2 = "SELECT * from DetailQry"

    Select Case Me!LoadFilter
    'Filter record source dependant on option checked
        Case 1
            strLoadFilterSQL = strSQL2
        Case 2
            strLoadFilterSQL = strSQL2 & " Where [LoadMethod] = 'Blk' or [Loadmethod]= 'CMGL' or [Loadmethod]= '?';"
        Case 3
            strLoadFilterSQL = strSQL2 & " Where [LoadMethod] = 'VND' or [Loadmethod]= 'BLT1';"


    'If filter applied with no option selected use default record source
        Case Else
            strLoadFilterSQL = strSQL2 & ";"
    End Select
    
'Set record source with filtered SQL
    Me.RecordSource = strLoadFilterSQL
    Me.Requery

End Sub

Private Sub ShiftFilter_AfterUpdate()
'Variable to hold filtered SQL string
Dim strShiftFilterSQL As String

'Set default record source of form
Const strSQL3 = "SELECT * from DetailQry"

    Select Case Me!ShiftFilter
    'Filter record source dependant on option checked
        Case 1
            strShiftFilterSQL = strSQL3
        Case 2
            strShiftFilterSQL = strSQL3 & " Where [LoadShift] = '1st';"
        Case 3
            strShiftFilterSQL = strSQL3 & " Where [LoadShift] = '2nd';"
        Case 4
            strShiftFilterSQL = strSQL3 & " Where [LoadShift] = '3rd';"

    'If filter applied with no option selected use default record source
        Case Else
            strShiftFilterSQL = strSQL3 & ";"
    End Select
    
'Set record source with filtered SQL
    Me.RecordSource = strShiftFilterSQL
    Me.Requery
End Sub

Private Sub StatusFilter_AfterUpdate()
'Variable to hold filtered SQL string
Dim strStatusFilterSQL As String

'Set default record source of form
Const strSQL4 = "SELECT * from DetailQry"

    Select Case Me!StatusFilter
    'Filter record source dependant on option checked
        Case 1
            strStatusFilterSQL = strSQL4
        Case 2
            strStatusFilterSQL = strSQL4 & " Where [Status] is not null and [status] <> 'Finished';"
        Case 3
            strStatusFilterSQL = strSQL4 & " Where [Status] = 'Backloading';"
        Case 4
            strStatusFilterSQL = strSQL4 & " Where [Status] = 'Deleted';"
        Case 5
            strStatusFilterSQL = strSQL4 & " Where [Status] = 'Finished';"
        Case 6
            strStatusFilterSQL = strSQL4 & " Where [Status] = 'Loading';"
        Case 7
            strStatusFilterSQL = strSQL4 & " Where [Status] = 'Not_Started';"
        Case 8
            strStatusFilterSQL = strSQL4 & " Where [Status] = 'Paused';"
        Case 9
            strStatusFilterSQL = strSQL4 & " Where [Status] = 'Pulled_To_Yard';"
                   
    'If filter applied with no option selected use default record source
        Case Else
            strStatusFilterSQL = strSQL4 & ";"
    End Select
    
'Set record source with filtered SQL
    Me.RecordSource = strStatusFilterSQL
    Me.Requery
End Sub

 

[bud31047]

Maybe this would allow you and the other more experienced programmers to propose the best possible solution for my situation."

Meaning more experienced than myself, not you AceMan.

 

[TheAceMan1]

bud31047 . . .

Not a good Idea having criteria and orderby on the same line, as in [blue]SortOption[/blue]. This makes it harder to parse thru criteria only. Anyway, I broke them out seperately in code as you'll see.

In the code module of the form, copy/paste the following routine:

[blue]Public Sub GroupFilter()
   Dim Ary(4) As String, preSQL As String, SQL As String, x As Integer
     
   preSQL = "SELECT * from DetailQry"
     
   If Not IsNull(Me!StatusFilter) Then
      Ary(0) = Choose(Me!StatusFilter, _
                  "", _
                  "([Status] is not null and [status] <> 'Finished') ", _
                  "([Status] = 'Backloading')", _
                  "([Status] = 'Deleted')", _
                  "([Status] = 'Finished')", _
                  "([Status] = 'Loading')", _
                  "([Status] = 'Not_Started')", _
                  "([Status] = 'Paused')", _
                  "([Status] = 'Pulled_To_Yard')")
   End If

   If Not IsNull(Me!ShiftFilter) Then
      Ary(1) = Choose(Me!ShiftFilter, _
                 "", _
                 "([LoadShift] = '1st')", _
                 "([LoadShift] = '2nd')", _
                 "([LoadShift] = '3rd')")
   End If

   If Not IsNull(Me!LoadFilter) Then
      Ary(2) = Choose(Me!LoadFilter, _
                "", _
                "([LoadMethod] = 'Blk' OR [Loadmethod]= 'CMGL' OR [Loadmethod]= '?')", _
                "([LoadMethod] = 'VND' OR [Loadmethod]= 'BLT1')")
   End If
                
   If Not IsNull(Me!SortOption) Then
      Ary(3) = Choose(Me!SortOption, _
                "([Doornum] Is Not Null)", _
                "([TrailerID]> '')", _
                "", _
                "", _
                "([Status] Is Not Null)")
   End If
      
   If Not IsNull(Me!SortOption) Then
      Ary(4) = Choose(Me!SortOption, _
             "Order By [Doornum]", _
             "Order By [TrailerID]", _
             "Order By [TripNumber]", _
             "Order By [DispatchDateTime]", _
             "Order By [Status]")
   End If

   [green]'Pack the final SQL[/green]
   For x = LBound(Ary) To UBound(Ary)
      If Ary(x) <> "" Then
         If x <> UBound(Ary) Then [green]'Handle Criteria[/green]
            If SQL <> "" Then
               SQL = SQL & " AND " & Ary(x)
            Else
               SQL = "WHERE " & Ary(x)
            End If
         Else [green]'Handle Order By[/green]
            If SQL <> "" Then
               SQL = SQL & " " & Ary(x)
            Else
               SQL = Ary(x)
            End If
         End If
      End If
   Next
      
   If SQL <> "" Then
      SQL = preSQL & " " & SQL & ";"
   Else
      SQL = preSQL & ";"
   End If
   
   [green]'Debug.Print SQL[/green]
   Me.RecordSource = SQL
                
End Sub[/blue]

Then in the [blue]AfterUpdate[/blue] event of the option groups, all you need is:

[blue]   Call GroupFilter[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[bud31047]

I will be away from this project for a few days. I will let you know how it works. I really appreciate your help.

Bud

 

[bud31047]

It works great AceMan. You are absolutely right mixing a criteria selection and an order by on the same line. I removed that from the code you provided and everything is good-to-go. Have a star.