Microsoft Access Query-Date Range Criteria-Form Boxes

Microsoft Access Query-Date Range Criteria-Form Boxes

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.

food sales table

Report Dates Table

The other table, ReportDates, just has one record, to store the selected start date and end date for the report.

ReportDates table

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.

Sales Report – Date Range form opens automatically

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.

pop-up calendar

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 results

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.

checks if those date boxes are empty

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

prompt for date with an input box

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

Microsoft Access Query-Date Range Criteria-Form Boxes

_______________

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.