Reel in needed records in Access just by using today’s date.


At a glance

If you’ve ever been frustrated from searching for a much needed record, yet couldn’t find it among all the other unneeded record entries, then you will find relief in managing your records more efficiently by narrowing down your data using the Date() function.

To utilize the Date() function we’ll:

  • Find all the records before a date that’s 30 days before the current date.
  • Find the records of orders that happened on a specific date and five days before today’s date.
  • Work with a date range to group your records by date.
  • Build a criterion that finds records between two specific dates.

One common criterion used to group records is by the date. For instance, you might need to see a list of customers who have had service performed on their cars in the past three months. But as the year continues, the date used as the starting point in the criterion expression (today’s date) changes. How do you control this moving target date in your query without modifying the query each time you run it? By showing you a few frequently used date expressions based on today’s date, you will be able to keep your queries up-to-date.

Store Dates

You might not realize that Access stores dates as serial numbers, just like Microsoft Excel. For example, the date 12/17/61 is represented by the serial number 22632. You can perform mathematical operations using dates just by manipulating the serial numbers. For instance, to find the date that’s 14 days after 6/4/04, just add 14 to the date’s serial number. We’ll use this fact to build expressions that limit query results to certain days or date ranges.

To create an expression for a date:

  1. Download the file from the URL at the beginning of this article.
  2. Open the file Orders.mdb.
  3. With the query objects displayed, double-click on the query named qryOrders By Date. This is a simple select query showing all of the records in the tblOrders table.
  4. Scroll down and note that the 336 order records occurred between February 4 and June 30.

Use today’s date to narrow the query results

To begin, let’s say we want to narrow the focus of the query results to show only those orders that are older than 30 days. To do this, we’ll need to modify the query.

A: This query criterion returns only those records that are older than 30 days before June 14.

To show orders older than 30 days:

  1. Click the View button View to design to display the query design grid.
  2. In the[...]
Join NowClose
Return to the ExcelSkillsSociety's homepage