Combining multiple toggle button filters

[jasdem]

I have a form that I use to filter a sub-form. In the form I have 4 toggle buttons that filter the corresponding fields in the sub-form quite well. What I would like to have is when one toggle is selected, the user can select a second or third toggle to further refine their inquiry. I am attaching the code that I used for the individual filters.

Private Sub Toggle_Filter_DOB_Click()


If Me.Toggle_Filter_DOB = True Then
     [Forms]![Phase 2]![FormPhase2_sub].Form.Filter = "DOB = #" & Me.ATS_DOB & "#"
     [Forms]![Phase 2]![FormPhase2_sub].Form.FilterOn = True
     
     Me.Toggle_Filter_DOB.Caption = "Filter On"
     
Else
     [Forms]![Phase 2]![FormPhase2_sub].Form.FilterOn = False
     Me.Toggle_Filter_DOB.Caption = "Filter Off"
End If


End Sub


Private Sub Toggle_Filter_FN_Click()


If Me.Toggle_Filter_FN = True Then
     [Forms]![Phase 2]![FormPhase2_sub].Form.Filter = "FirstName = '" & Right([Student_First_Name], (Len([Student_First_Name]) - 1)) & "'"
     [Forms]![Phase 2]![FormPhase2_sub].Form.FilterOn = True
     Me.Toggle_Filter_FN.Caption = "Filter On"
Else
     [Forms]![Phase 2]![FormPhase2_sub].Form.FilterOn = False
     Me.Toggle_Filter_FN.Caption = "Filter Off"
End If


End Sub


Private Sub Toggle_Filter_GLN_Click()


If Me.Toggle_Filter_GLN = True Then
     [Forms]![Phase 2]![FormPhase2_sub].Form.Filter = "LastName = '" & Me.Guardian_Last_Name & "'"
     [Forms]![Phase 2]![FormPhase2_sub].Form.FilterOn = True
     Me.Toggle_Filter_GLN.Caption = "Filter On"
Else
     [Forms]![Phase 2]![FormPhase2_sub].Form.FilterOn = False
     Me.Toggle_Filter_GLN.Caption = "Filter Off"
     
End If


End Sub


Private Sub Toggle_Filter_LN_Click()


If Me.Toggle_Filter_LN = True Then
     [Forms]![Phase 2]![FormPhase2_sub].Form.Filter = "LastName = '" & Me.Student_Last_Name & "'"
     [Forms]![Phase 2]![FormPhase2_sub].Form.FilterOn = True
     Me.Toggle_Filter_LN.Caption = "Filter On"
Else
     [Forms]![Phase 2]![FormPhase2_sub].Form.FilterOn = False
     Me.Toggle_Filter_LN.Caption = "Filter Off"
End If


End Sub

Any help would be appreciated.

[June7]

You can build the filter string based on conditional code. Review: http://allenbrowne.com/ser-62code.html

Something like:

Dim strWHERE As String
'code to build the string
...
[Forms]![Phase 2]![FormPhase2_sub].Form.Filter = strWHERE
[Forms]![Phase 2]![FormPhase2_sub].Form.FilterOn = True

[jasdem]

Thanks for the lead. I put together the new code but seem to have some syntax errors.

[code]

Private Sub Toggle_Filter_DOB_Click()


Dim strWhere As String
Dim lngLen As Long


If Me.Toggle_Filter_DOB = True Then
strWhere = strWhere & "DOB = #" & Me.ATS_DOB & "#" And ""
End If


If Me.Toggle_Filter_FN = True Then
strWhere = strWhere & "FirstName = '" & Right([Student_First_Name], (Len([Student_First_Name]) - 1)) & "'" And ""
End If


If Me.Toggle_Filter_LN = True Then
strWhere = strWhere & "LastName = '" & Me.Student_Last_Name & "'" And ""


If Me.Toggle_Filter_GLN = True Then
strWhere = strWhere & "LastName = '" & Me.Guardian_Last_Name & "'" And ""
End If


lngLen = Len(strWhere) - 5


[Forms]![Phase 2]![FormPhase2_sub].Form.Filter = strWhere
[Forms]![Phase 2]![FormPhase2_sub].Form.FilterOn = True




End Sub

[\code]

[June7]

Check Allen's code again. Need to trim 5 characters from the end of strWhere. You are missing the If Then structure after line to set the lnglen variable.

[jasdem]

Thanks for all the help. I was able to produce a code that works

[code]

Private Sub Toggle_Filter_LN_Click()


Dim strWhere As String
Dim lngLen As Long
'Const conJetDate = "\#mm\/dd\/yyyy\#"


If Me.Toggle_Filter_DOB = True Then
strWhere = strWhere & "(DOB = #" & Me!ATS_DOB & "#) And "
Me.Toggle_Filter_DOB.Caption = "Filter On"
Else
Me.Toggle_Filter_DOB.Caption = "Filter Off"
End If


If Me.Toggle_Filter_FN = True Then
strWhere = strWhere & "(FirstName = '" & Right(Me!Student_First_Name, Len(Me!Student_First_Name) - 1) & "') And "
Me.Toggle_Filter_FN.Caption = "Filter On"
Else
Me.Toggle_Filter_FN.Caption = "Filter Off"
End If


If Me.Toggle_Filter_LN = True Then
strWhere = strWhere & "(LastName = '" & Me!Student_Last_Name & "') And "
Me.Toggle_Filter_LN.Caption = "Filter On"
Else
Me.Toggle_Filter_LN.Caption = "Filter Off"
End If


If Me.Toggle_Filter_GLN = True Then
strWhere = strWhere & "(LastName = '" & Me!Guardian_Last_Name & "') And "
Me.Toggle_Filter_GLN.Caption = "Filter On"
Else
Me.Toggle_Filter_GLN.Caption = "Filter Off"
End If


lngLen = Len(strWhere) - 5


If lngLen <= 0 Then
Else
strWhere = Left$(strWhere, lngLen)



[Forms]![Phase 2]![FormPhase2_sub].Form.Filter = strWhere
[Forms]![Phase 2]![FormPhase2_sub].Form.FilterOn = True


End If
End Sub

[\code]