Others

How to Use Access Parameter Dates in Report Headings and PDF File Names

Jason Miller Oct 23, 2026 868 views

Question details

The user needs to display start and end dates in a Microsoft Access report heading and use those exact date values to dynamically construct the file name when exporting the report to PDF.

Use Access Parameter Dates in Report Headings and Exported PDF File Names

Product: Microsoft Access
Device & OS: not provided
Scenario: Exporting an Access report to PDF and needing to dynamically name the exported file based on the date parameters provided by the user.
Observed behavior: Standard query prompts do not easily expose their inputted date values to the VBA code responsible for generating the exported PDF file name.

Before you start

Ensure your Access database is backed up and that you have basic familiarity with creating forms and using VBA (Visual Basic for Applications) in Microsoft Access.

Solution 1Recommended

Use Form Controls Instead of Standard Query Prompts

Replace standard query parameter prompts with a dedicated input form. This approach allows VBA to read the dates directly for the PDF filename while the query and report can reference the same form controls.

Standard query prompts only exist in memory while the query is running, making it difficult for VBA to capture those values for file naming. By creating a parameter form, the date values remain accessible to your VBA export script, your query criteria, and your report headings simultaneously.

Create a parameter form

In Access, create a new blank form and name it 'frmParams'. Add two unbound text boxes to this form and name them 'txtStart' and 'txtEnd'. Set their format to Date/Time.

Modify the query criteria

Open the report's underlying query in Design View. In the criteria row for your date field, replace the bracketed prompts with references to your form controls: Between [Forms]![frmParams]![txtStart] And [Forms]![frmParams]![txtEnd].

Update report headings

Open your report in Design View. Add a text box to the report header and set its Control Source to: ="Report from " & [Forms]![frmParams]![txtStart] & " to " & [Forms]![frmParams]![txtEnd].

Write the VBA export script

Add a command button to 'frmParams'. In its On Click event procedure, assign the dates to variables and format them to avoid invalid file characters (e.g., slashes). For example: strStart = Format(Me.txtStart, "yyyymmdd").

Execute the PDF export

Below the variables in your VBA script, use the DoCmd.OutputTo method to generate the PDF. Combine the report name and your formatted date variables to build the file path string: DoCmd.OutputTo acOutputReport, "YourReportName", acFormatPDF, "C:\YourFolder\Report_" & strStart & "_" & strEnd & ".pdf".

Use Form Controls Instead of Standard Query Prompts

Reusable Function Tip: If you have multiple reports that require this functionality, you can place the DoCmd.OutputTo logic inside a custom VBA module function. Pass the report name and the date variables as arguments to streamline your database code.

Free Microsoft Office alternative

Discover WPS Office for Your Daily Document and PDF Needs

While advanced relational databases require Microsoft Access, WPS Office is the perfect, lightweight alternative for handling your everyday reports, spreadsheets, and presentations. Enjoy powerful PDF export tools without the heavy subscription fees.

1. Download the Installer: Visit the official WPS website and click the free download button for your operating system.
2. Install the Suite: Run the downloaded setup file and follow the straightforward on-screen instructions to install WPS Office.
3. Open and Export Documents: Launch WPS Office to instantly open your existing files or create new documents, exporting them to PDF with a single click.

Seamless format compatibility with Microsoft Word, Excel, and PowerPointBuilt-in PDF toolkit to effortlessly convert, edit, and merge PDF reportsFree, lightweight, and consumes minimal system resourcesFamiliar ribbon interface ensures zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I use standard Access query prompts in my VBA exported PDF file name?

Standard bracketed query prompts only capture values during the execution of the query. Once the query runs, those values are discarded from memory, meaning your VBA script cannot retrieve them to append to a file name.

How do I prevent 'invalid file path' errors when exporting a PDF with dates?

Standard date formats often include slashes (e.g., 10/12/2023), which are illegal characters in Windows file names. You must use the VBA Format() function to convert the date to a safe string, such as Format(Me.txtStart, "yyyymmdd"), before appending it to the file path.

Can I close the parameter form after clicking the export button?

The parameter form ('frmParams') must remain open while the report runs so that the query criteria and the report header text boxes can read the date values. You can write VBA to automatically close the form after the DoCmd.OutputTo command finishes.

Office Quick Solutions