Filter a Microsoft Access List Box by Combo Boxes and Dates
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.
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.
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.
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.
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
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.
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.

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.

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.




