In Part 1 of this series, we covered how to search records using text-based fields — like finding an employee by name — and how to filter by fixed category values like Gender using a Combo Box or Option Group. If you haven’t read that yet, it’s a good starting point.
In this part, we go further. We’re covering two types of searches that come up constantly in real databases: searching by number values (with Greater Than and Less Than logic) and filtering records by date. Both of these are essential if you’re working with employee data, sales records, or anything that involves ages, salaries, or time periods.
Searching by Number Values
Text searches are straightforward — you’re looking for an exact match. But numbers are different. When someone wants to find employees over a certain age, or salaries within a range, you need Greater Than, Less Than, and Between logic built into your form. Here’s how to do it three ways.
Method 1: Exact Number Match (Simple Textbox + Button)
This is the starting point. You type a number, click the button, and the form filters to show only records where the field matches that exact number.
You need two things on your form: a textbox (for entering the number) and a command button (to trigger the search). Name them clearly — for example, Search5 for the textbox and CmdSearch5 for the button.
Add this VBA code to the On Click event of the button:
vba
Private Sub CmdSearch5_Click()
Dim strGenderGFind As String
'Criteria
strGenderGFind = "[Age] = " & Me.Search5
Me.Form.Filter = strGenderGFind
Me.Form.FilterOn = True
End SubWhen the user types 30 and clicks the button, the form shows only employees aged exactly 30. Simple and effective for exact lookups.
Method 2: Search with Greater Than / Less Than Factor
Exact matches are useful, but most real scenarios need more flexibility. You might want to see all employees older than 35, or all salaries below 50,000. This method adds that flexibility by pairing the textbox with a Combo Box that lets the user choose a comparison factor.
Set the Combo Box’s Row Source Type to Value List and enter these values: None;Greater Than;Less Than
Then attach this VBA code to the On Click event of the button:
vba
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 SubThe logic here is straightforward: the code checks which option the user selected in the Combo Box. If they picked None, it clears the filter. If they picked Greater Than, it applies [Age] > value. If they picked Less Than, it applies [Age] < value.
The None option is important — it gives users a way to reset back to showing all records without clicking an extra button.
Method 3: Filter Records Between Two Number Values
Sometimes you need a range — for example, employees between the ages of 25 and 40. For this, you create two textboxes: one for the lower boundary (Greater Than) and one for the upper boundary (Less Than). Label them clearly on the form so users know which is which.
Name them Search6A and Search6B, add a button, and use this code:
vba
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 SubIf the user enters 25 in the first box and 40 in the second, the form filters to show only records where Age is greater than 25 AND less than 40. Clean and practical.
Filtering Records by Date
Date filtering is one of the most common things people need from a database — and also one of the areas where people get confused, because dates in Access need to be wrapped in # symbols in the filter criteria (not quotes like text values). Once you understand that, the rest is quite simple.
We’ll use the [Hire Date] column in an Employees table as the reference field throughout these examples.
Method 1: Single Date Filter
This is the simplest date search — the user types a specific date and the form shows only records that match exactly.
You need a textbox (for the date input) and a button. Add this code to the button’s On Click event:
vba
Private Sub CmdSearch7_Click()
Dim strGenderFind As String
'Criteria
strGenderFind = "[Hire Date] = #" & Me.Search7 & "#"
Me.Form.Filter = strGenderFind
Me.Form.FilterOn = True
End SubNotice the # symbols around the date value. That’s the Access syntax for dates — without them, the filter won’t work correctly.
Alternative — Table Query Method: If you prefer not to use VBA, you can build a query with all the necessary fields from your table, then reference the date textbox directly in the criteria row of the [Hire Date] field. The button then simply refreshes the form to apply the query’s filter.
To clear the search, either empty the date textbox manually or create a dedicated “Clear” button that sets the textbox to Null and refreshes the form.
Method 2: Filter Between Two Dates
This is where date filtering really becomes powerful. Instead of a single date, the user picks a start date and an end date — and the form shows every record that falls within that range.
Create two textboxes in the form header: one for the “From” date and one for the “To” date. Name them txtDtFrom and txtDtTo, and add a search button.
Apply this code to the button’s On Click event:
vba
Private Sub CmdBetweenDt_Click()
Dim strGenderFind As String
'Criteria
strGenderFind = "[Hire Date] between #" & Me.txtDtFrom & "# and #" & Me.txtDtTo & "#"
Me.Form.Filter = strGenderFind
Me.Form.FilterOn = True
End SubThe between ... and ... syntax tells Access to include every record where the Hire Date falls on or between the two dates entered. It’s clean, readable, and works well.
Alternative — Table Query Method: Build a query and add both textbox references into the criteria row of the [Hire Date] field like this:
Between FormTxtDateFrom And FormTxtDateToJust replace FormTxtDateFrom and FormTxtDateTo with the actual names of your textboxes. The button refreshes the form. Same result, no VBA required.
A Quick Note on Clearing Filters
Across all these methods — number filters and date filters — you’ll want a way for users to reset the form back to showing all records. The cleanest approach is a dedicated Clear button that does two things: sets the search textboxes back to Null (or empty), and refreshes the form with FilterOn = False.
Without a clear button, users can get stuck in a filtered view with no obvious way out, which makes the form frustrating to use.
Putting It All Together
By the end of both parts of this series, you now have a solid toolkit for building search functionality into any MS Access form:
| Method | What It Does | Needs VBA? |
|---|---|---|
| Combo Box Wizard | Find a record by text field (name, city, etc.) | No |
| Advanced Combo Box | Same, with auto-open drop-down and sorted list | Small amount |
| Value List Combo Box | Filter by fixed category (gender, status) | Yes |
| Option Group | Same as above, with visible radio buttons | Yes |
| Exact Number Search | Filter by matching a specific number | Yes |
| Number with Factor | Filter with Greater Than / Less Than | Yes |
| Between Two Numbers | Filter within a number range | Yes |
| Single Date Filter | Filter by an exact date | Yes / Optional |
| Between Two Dates | Filter within a date range | Yes / Optional |
For most practical databases, you’ll combine several of these on the same form — a text search at the top, a category filter alongside it, and a date range search for time-based data. Together they cover nearly every search scenario a typical user will need.
Watch the full tutorial on YouTube: Multiple Ways to Search Records in MS Access Form Part 1 by Skill Header, and download the practice files at skillheader.com.