Others

Filter a Microsoft Access List Box by Combo Boxes and Dates

Adam DavisAdam Davis Nov 20, 2026 869 views

Question details

Display Project records in an Access form list box using department, technician, and date filters.

Product
Microsoft Access
Device & OS
not provided
Scenario
A form has Department, Technician, Date From, and Date To controls that should filter a list of projects.
Observed behavior
The list box should show records matching the selected filters and update when a filter changes.
Before you start

In the form's Design View, check the list box and filter control names, the Project table field names, and the combo boxes' bound columns. The example assumes the combo boxes return numeric IDs and the date controls contain dates without times.

Solution 1Recommended

Update the list box RowSource when a filter changes

Use a VBA procedure to build the list box query from the selected filters and refresh the results after each change.

This example treats blank controls as optional filters. The date range includes the entire Date To day, even if ProjectDate contains a time.

Replace Project, DepartmentID, TechnicianID, ProjectDate, and the control names with the names in your database. If a combo box returns text rather than a numeric ID, adjust the SQL to quote the value and escape apostrophes; do not use the numeric-ID code unchanged.

1
Set up the list box

Open the form in Design View, select the project list box, and use the Property Sheet to confirm its Name is ProjectList and its Row Source Type is Table/Query. Adjust its column settings to match the fields you want to display.

2
Add the filter procedure

Open the form's VBA module and add this procedure: Private Sub UpdateProjectList() Dim sql As String Dim dateFromValue As Date Dim dateToValue As Date sql = "SELECT Project.* FROM Project WHERE 1=1" If Not IsNull(Me.DepartmentCombo.Value) Then If Not IsNumeric(Me.DepartmentCombo.Value) Then MsgBox "Select a numeric department ID." Exit Sub End If sql = sql & " AND DepartmentID = " & CLng(Me.DepartmentCombo.Value) End If If Not IsNull(Me.TechnicianCombo.Value) Then If Not IsNumeric(Me.TechnicianCombo.Value) Then MsgBox "Select a numeric technician ID." Exit Sub End If sql = sql & " AND TechnicianID = " & CLng(Me.TechnicianCombo.Value) End If If Not IsNull(Me.DateFrom.Value) Then If Not IsDate(Me.DateFrom.Value) Then MsgBox "Enter a valid Date From." Exit Sub End If dateFromValue = DateValue(Me.DateFrom.Value) End If If Not IsNull(Me.DateTo.Value) Then If Not IsDate(Me.DateTo.Value) Then MsgBox "Enter a valid Date To." Exit Sub End If dateToValue = DateValue(Me.DateTo.Value) End If If Not IsNull(Me.DateFrom.Value) And Not IsNull(Me.DateTo.Value) Then If dateFromValue > dateToValue Then MsgBox "Date From must not be later than Date To." Exit Sub End If End If If Not IsNull(Me.DateFrom.Value) Then sql = sql & " AND ProjectDate >= #" & Format$(dateFromValue, "mm\/dd\/yyyy") & "#" End If If Not IsNull(Me.DateTo.Value) Then sql = sql & " AND ProjectDate < #" & Format$(DateAdd("d", 1, dateToValue), "mm\/dd\/yyyy") & "#" End If Me.ProjectList.RowSource = sql Me.ProjectList.Requery End Sub

3
Connect the filter controls

For each of DepartmentCombo, TechnicianCombo, DateFrom, and DateTo, select the control in Design View and set Property Sheet > Event > After Update to [Event Procedure]. Click the builder button and add UpdateProjectList inside each generated AfterUpdate procedure.

4
Test the filters

Open the form in Form View. Select a department and technician, enter a date range, and change or clear each control to verify that ProjectList updates. Check that projects with times on Date To are included.

Update the list box RowSource when a filter changes
Invalid filters: If validation fails, the procedure displays a message and leaves the previous list results in place. The example expects numeric IDs that fit an Access Long Integer.
Free Microsoft Office alternative

Review exported project lists in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for working with exported project lists. Open compatible spreadsheet files and use filters to review rows; it does not replace or fix an Access form or its VBA code.

Filter exported project rows by department, technician, or date in a spreadsheet.Open and edit Microsoft Excel XLSX files for an easy transition from Office.Use a familiar spreadsheet interface to review and share project data.Work with a free, lightweight Office alternative.
microsoft office alternative - wps office

Frequently Asked Questions

How do I make the list update after I select a department?

In the department combo box's Property Sheet, set Event > After Update to [Event Procedure] and call UpdateProjectList from the generated event procedure.

Can I leave some filters blank?

Yes. The example adds a condition only when its control is not Null, so a blank department, technician, or date control does not restrict that field.

Why use a date before the day after Date To?

The condition ProjectDate < Date To plus one day includes all times on the selected end date. A condition ending at midnight on Date To could exclude records later that day.

What if my combo box stores department names instead of IDs?

Check the combo box's Bound Column in the Property Sheet. If it returns text, change the SQL condition to use a quoted text value and escape any apostrophes, rather than converting the value with CLng.