My Access Tips for Custom Microsoft Access

Application Development

by Matthew V Carmichael

Need Help?

My Tips



Report Menu - MS Access example application (with calendar control and Excel export)

This application (Report Menu Example) contains an easy to use report menu that allows for report grouping and automatic date range population.  This application also demonstrates how to use the calendar control and export report data to Excel.

Example application features:

  • Reports can be organized into specific Report Groups making it easier for the user to find and access their desired report.

  • When the user selects (single clicks) on a report, a description of the report is displayed providing the user with additional information. 
  • For reports where the user wants to see information for a specific date range, this example contains a drop down containing standard ranges.  Start and End dates are automatically populated for:
    • All
    • Current Month
    • Current Week
    • Current Year
    • Current YTD
    • Next Month
    • Next Week
    • Next Year
    • Previous Month
    • Previous Week
    • Previous Year
    • Previous YTD
    • Today
    • Tomorrow
    • Yesterday


These predetermined ranges are store in a local table so the developer can easily add or remove ranges.

  • Users can specify their own range by manually entering start and end dates or using a pop-up calendar (Calendar Control).
  • The application allows the user to select the field to apply the date range filter to (if the report contains more than one).  The developer can simply add the date range fields to the tsysReports table.

  • The Export feature will export the report’s underlying data to Excel.  The “Analyze with Excel” feature built into MS Access exports more then just the report’s data.  Along with the data, text formatting and calculations are exported.  Many users just want the data so they can analyze the information, create custom calculations, and format the data.  The export feature in this example application creates a query based off the report’s recordsource and filter properties.  Then the query is exported to Excel so only the data appears – voila, no formatting.  IMPORTANT:  in order for this feature to work, the report’s recordsource must be a SQL statement (do not use a table or query name).