To make a Microsoft Access report easy to use, enter date range start and end dates on an Access form. Then, click a button to see all the sales orders in the selected date range.
Microsoft Access Date Range Boxes
In my latest sample file, there is a small Access database, with sales orders from a fictional food company.
The database has two tables, one query, and one form.
The main table, FoodSales, has order details, with a unique order ID for each record.
Report Dates Table
The other table, ReportDates, just has one record, to store the selected start date and end date for the report.
Sales Report Form
When you open the database, the Sales Report – Date Range form opens automatically – I set that in the File > Options > Current Database settings.
The form is based on the ReportDate table, and has two textboxes, for the Start date and the End date.
Change the Dates
To change the date range, click in one of the text boxes, then click the Calendar icon.
In the pop-up calendar, you can click on a date, or use the arrows to scroll to months in the past, or in the future.
Show the Orders
After you set the date range, click the Show Orders button, to see the food sales orders from that range.
The button runs a very simple VBA procedure, to open the query named qryOrders_RptDateRange
In the screen shot below, you can see the first few rows in the query results.
The selected start date was February 1, 2023, and the first record in the query result shows an order from that date.
Query Criteria Date Range
In the query, this entry is in the criteria row for the OrderDate field:
- Between [Forms]![frmReport_DateRange].[RptStart] And [Forms]![frmReport_DateRange].[RptEnd]
The query also checks if those date boxes are empty. If both are empty (Is Null), the query returns all of the food sales records.
More Access Query Date Criteria Examples
For more date criteria examples, go to my earlier blog post: Access Date Criteria Examples
The examples show how to use date criteria in several ways:
- type exact dates in query criteria
- prompt for date with an input box
- get dates from form text boxes
Get the Sample Database
Click the link here to download the zipped Food Sales Database file (31 kb), from my Contextures site.
The file is in Microsoft Access accdb format, and there is a macro on the Sales Report form button, to show sales orders for the selected date range
The Food Sales database has sample records, based on the fictional sales orders from my Contextures Excel website.
___________________________
Microsoft Access Query-Date Range Criteria-Form Boxes
_______________