Quick Filters in a Split Form Datasheet

Question

Creating an access database for work. Users will use a split form with only the datasheet visible to review and manipulate numeric data. On the form I have built in quick filters that consist of of textboxes in which the values are either raised or lowered with arrow buttons that have on-click events. I currently have the text boxes linked to the recordsource query criteria.

With all of this stated, the problem that I am having is that I need the filter to act in the following manner:

If the value in the text box equals 0 I want to see all records. If the value is greater than 0, I want all records greater than or equal to the text box value to show. Finally, if the value in the text box is less than 0, I want to see all values less than or equal to 0.

I have considered trying to use multiple sql statements but I typically have about 3 of these quick filters on each form, and my project will eventually have about 20 forms. That is a lot of sql statements to potentially mess up.

What ideas do you guys have to solve this problem? I really need help.

Answer 1

If you only have 1 textbox on each form, then you may want to consider using the form's Filter property:

Private Sub txtFilter_AfterUpdate()
    On Error GoTo E_Handle
    If Not IsNull(Me!txtFilter) Then
        If IsNumeric(Me!txtFilter) Then
            Select Case Me!txtFilter
                Case Is < 0
                    Me.Filter = "Price<=0"
                    Me.FilterOn = True
                Case 0
                    Me.FilterOn = False
                Case Is > 0
                    Me.Filter = "Price>=" & Me!txtFilter
                    Me.FilterOn = True
            End Select
        End If
    End If
sExit:
    On Error Resume Next
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "Form2!txtFilter_AfterUpdate", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

If need to have multiple filters, then consider moving the filter creation to a procedure by itself that handles all cases, and just call this procedure from each text box. You may have to think about the logic here of what happens if one text box is 0 (No filter), but another text box is 5 (display all values >= 5) and another text box is -3 (display all values <= 0):

Private Sub txtFilter2_AfterUpdate()
    On Error GoTo E_Handle
    Call sFilterForm2
sExit:
    On Error Resume Next
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "Form2!txtFilter2_AfterUpdate", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

Private Sub sFilterForm2()
    On Error GoTo E_Handle
    Dim strSQL As String
    If Not IsNull(Me!txtFilter) Then
       If IsNumeric(Me!txtFilter) Then
           Select Case Me!txtFilter
               Case Is < 0
                   strSQL = " AND Price<=0 "
               Case 0
               Case Is > 0
                   strSQL = strSQL & " AND Price>=" & Me!txtFilter
           End Select
       End If
    End If

    If Not IsNull(Me!txtFilter2) Then
       If IsNumeric(Me!txtFilter2) Then
           Select Case Me!txtFilter2
               Case Is < 0
                   strSQL = " AND Price<=0 "
               Case 0
               Case Is > 0
                   strSQL = strSQL & " AND Price>=" & Me!txtFilter2
           End Select
       End If
    End If

    If Len(strSQL) > 0 Then
        strSQL = Mid(strSQL, 5)
        Me.Filter = strSQL
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If
sExit:
    On Error Resume Next
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "Form2!sFilterForm2", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

Regards

Answer 2

Given a text box control CriteriaField1 and a corresponding field to filter Field1 in the record source I would use this:

Private Sub CriteriaField1_AfterUpdate()
    Const FIELD_NAME As String = "Field1"

    Dim value As Long
    value = Nz(Me("Criteria" & FIELD_NAME).Value, 0)

    Dim condition As String
    condition = FIELD_NAME & IIf(value < 0, " <= 0", " >= " & value)

    Me.FilterOn = value <> 0
End Sub

If you need to combine multiple fields to a filter condition, you would have to use and set form-global variables instead of local ones.

Answer 3

You could call a helper function which holds a set of arrays and builds and sets the filter dynamically:

Private Sub Filter0_AfterUpdate()

    SetFilter

End Sub


Private Sub Filter1_AfterUpdate()

    SetFilter

End Sub


Private Sub Filter2_AfterUpdate()

    SetFilter

End Sub


Private Sub SetFilter()

    Dim FieldNames()    As Variant
    Dim TextboxNames()  As Variant
    Dim Criteria()      As String

    Dim Index           As Integer
    Dim Value           As Long

    ' Specify the field names to filter on.
    FieldNames = Array("Quantity", "Stock", "Size")
    ' Specify the names of the textboxes to enter filter values.
    TextboxNames() = Array("Filter0", "Filter1", "Filter2")

    ReDim Criteria(LBound(TextboxNames) To UBound(TextboxNames))

    For Index = LBound(Criteria) To UBound(Criteria)
        Value = Val(Nz(Me(TextboxNames(Index)).Value))
        If Value < 0 Then
            Criteria(Index) = FieldNames(Index) & " <= 0"
        ElseIf Value > 0 Then
            Criteria(Index) = FieldNames(Index) & " >= " & CStr(Value)
        Else
            Criteria(Index) = "True"
        End If
    Next

    ' Assemble and apply the filter.
    Me.Filter = Join(Criteria, " And ")
    Me.FilterOn = True

    Debug.Print Me.Filter

End Sub