How to Do Date Range in Access Reports


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:

    star wars,4,4/1/2009
    star wars,7,1/3/2009
    star wars,6,2/7/2009
    star wars,3,7/31/2009

    (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.

Related Searches


  • Photo Credit BananaStock/BananaStock/Getty Images
Promoted By Zergnet


You May Also Like

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!