How to Search Records using Number Values with Factors in MS Access

Welcome to a very important method to search records using Number Values (Numeric Input). For example, sometimes we need to analyze the age or salaries of the employees with factors of Greater Than and Less Than, in this blog, we will go through a complete process on how to do this.

We will use the [Age] column in the Employees’ data.

A textbox and a button are required in your form. Name the textbox and button. Start VBA coding from the “On Click” event of the button. Here is the code:

Private Sub CmdSearch5_Click()
         Dim strGenderGFind As String
         'Criteria
         strGenderGFind = "[Age] = " & Me.Search5
         Me.Form.Filter = strGenderGFind
         Me.Form.FilterOn = True
                
End Sub

Method 2: Search Number Value with Factor

Use a Combo box drop-down list with the textbox and put the manual values of “All, Greater Than, and Less Than” as list items for the combo box.

Start VBA coding from the “On Click” event of the button. Here is the code:

Private Sub CmdSearch5_Click()
If Me.txtAgeFactor = "None" Then
    Me.Search5 = Null
    Me.Form.FilterOn = False
        Else
            If Me.txtAgeFactor = "Greater Than" Then
                Dim strGenderGFind As String
                'Criteria
                strGenderGFind = "[Age] > " & Me.Search5
                Me.Form.Filter = strGenderGFind
                Me.Form.FilterOn = True
                Else
                    If Me.txtAgeFactor = "Less Than" Then
                        Dim strGenderLFind As String
                        'Criteria
                        strGenderLFind = "[Age] < " & Me.Search5
                        Me.Form.Filter = strGenderLFind
                        Me.Form.FilterOn = True
                End If
        End If
End If
End Sub

Method 3: Search and Filter Records Between 2 Number Values

To get the outcome of the desired records in between 2 numeric values, you need to create 2 textboxes and label them as a “Greater Than” value and a “Less Than” value.

Create a button and apply this VBA code:

Private Sub CmdSearch6_Click()
Dim strGenderFind As String
    'Criteria
    strGenderFind = "[Age] > " & Me.Search6A & "AND [Age] <" & Me.Search6B
    Me.Form.Filter = strGenderFind
    Me.Form.FilterOn = True
End Sub