Applying date ranges to reports allows you to present and view data more clearly than you could with a default report. The tools in Microsoft Access' report design view can easily configure reports to make use of specific date ranges. You can apply date ranges to group a database table's records by date; for example, you can display records that share the same month together. Or, you can use date ranges as filters, so the report only displays the records within the specified date range.
Create a sample database of movie sales in Access, so you have data with which to create some sample reports. Use your own sample data if it has at least one date field. Or, use the following table; enter the first row as field names:
(The remaining steps will assume you're using this sample table, though they will work on your own tables.)
Press "Control-S" to save the table and name it "MovieSales" when prompted.
Create a new report from the table by pressing "Create," followed by "Report," on the Access toolbar. (Access 2003 users, press "Objects," then "Reports," followed by "New.") Notice the initial position of the DateSold field in relation to the other fields.
Right-click on the new report's "DateSold" field and select "Group On DateSold." Notice that Access moves the DateSold field to the left of the report and that all records are now grouped based on quarterly date ranges. (Records with sale dates between 1/1/2009 and 3/1/2009 are grouped apart from records with sale dates in other quarters of the year.)
Click on the "More" text in the "Group, Sort & Total" window, below the main report. Look for the drop-down list that displays the text "by quarter." Click on that list's arrow and select "by Month" to change the grouping date range from quarters of a year to months. Notice that the report now groups records by the month of the sale date.
Press the "X" to the right of the grouping and sorting options to delete all groups for the report. Click "Yes" on the dialog box that warns you about the deletion.
Apply a date range now to filter a report's records. Right-click on the DateSold field. Then, select "Date Filters," followed by "Between." In the dialog box that pops up, enter the following dates for the box's "Oldest:" and "Newest:" fields:
1/1/2009 (for "Newest:") and
1/31/2009 (for "Oldest:")
Press "OK" on the dialog box; notice the result on the report. Only records for sales in January 2009 are shown.
Right-click on any blank space on the report and select "Report Properties." Click on the "Data" tab of the "Property Sheet" window that displays to the right of the report window. Notice the text in the "Filter" field, which indicates how the report was filtered.
Click inside the "Filter" field and change just the dates to read as follows:
... Between #1/1/2009# And #6/30/2009#
Click on any blank area of the report to update it. Notice that records for sales in the first half of the year are now displayed.
- Photo Credit BananaStock/BananaStock/Getty Images
How to Calculate a Date Range
At one time or another, we all have needed to figure the number of days, weeks, or months between two particular dates....
How to Create Aging Reports in Access
An aging report is a report that shows accounts receivable amounts grouped by age. The report makes it easy to spot customers...
How to Filter Records Using Date Range in Visual Basic 6
Searching records using date ranges are oftentimes used in computer programming to filter specific records that fall within the dates specified. These...
How to Query by Date in Access
One of the recognized advantages of the "Query by Example" (QBE) feature in Microsoft Access is the ability to provide a literal...
How to Access a Query Between Two Dates
Access queries let you extract data out of your database. When querying dates, you must utilize a # symbol with the dates....