---

MS Access 2003: Filter report results using a date parameter

This MSAccess tutorial explains how to filter report results using a date parameter in Access 2003 (with screenshots).

Question: In Microsoft Access 2003/XP/2000/97, I have a table with two date fields, one is [CDate] the other is [NewDate].

[CDate] will always be earlier than [NewDate]. [NewDate] may either have a date or be left blank (ie: null value).

I want to create a report that I enter a date parameter and the report will be generated using [CDate] if that is the only date available or generated using [NewDate] if that field is being used.

Answer: You can add a parameter to the "Record Source" property of an Access report.

We've created an Access sample that you can download.

Download Access 2000 version

In this example, we've created a button that opens a report called rptSuppliers. When the report is launched, you will be prompted for the date parameter as follows:

The rptSuppliers report will then open and display only those results where [CDate] matches the date parameter if [NewDate] is null OR [NewDate] matches the date parameter if [NewDate] is not null.

If you look at the Record Source property for the rptSuppliers, it looks as follows:

SELECT Suppliers.*
FROM Suppliers
WHERE (((Suppliers.NewDate) Is Null) AND ((Suppliers.CDate)=[Enter a date])) OR (((Suppliers.NewDate) Is Not Null And (Suppliers.NewDate)=[Enter a date]));