Printing parameters in an Access parameter query

I am developing an Access report based开发者_StackOverflow中文版 on a query that has a date range as a parameter, like this

Between [Enter Start Date (mm/dd/yyyy)] And [Enter End Date (mm/dd/yyyy)]

How do I include the values entered for the start date and the end date in the report?

Thanks in advance.

GRB

You have to select the input values as columns in the query, using the exact names like in the Where clause.

Your query will probably look something like this:

select 
    Column1,
    Column2,
    DateColumn
from
    MyTable
where
    DateColumn between [Enter Start Date (mm/dd/yyyy)]
    and [Enter End Date (mm/dd/yyyy)]

To include the input values in the query, you have to change the query like this:

select 
    Column1, 
    Column2, 
    DateColumn, 
    [Enter Start Date (mm/dd/yyyy)] as StartDate, 
    [Enter End Date (mm/dd/yyyy)] as EndDate
from 
    MyTable
where 
    DateColumn between [Enter Start Date (mm/dd/yyyy)] 
    and [Enter End Date (mm/dd/yyyy)]

You can use any alias you want for the input values (I used StartDate and EndDate), as long as the actual column names ([Enter Start Date (mm/dd/yyyy)] and [Enter End Date (mm/dd/yyyy)]) are exactly the same.

Of course this means that the query will contain the input values in every row, but you don't need to show them in every row in the report.
Just put the fields bound to StartDate and EndDate in the header or in the footer of the report, and the values will show up in the report only once.

DoCmd.OpenReport has some additional parameters for the filter, a where clause and most importantly OpenArgs. During the report load event you can capture these arguments to customize the Data Source for your report (EG: The range to select from) and set the value of Labels on the report to the input provided prior to opening up the report.

http://msdn.microsoft.com/en-us/library/bb238032%28v=office.12%29.aspx provides an overfiew of this functionality.

I envision a form where the user selects a date range from and hits OK. The report is called to open through the button click event and the date range (once validated on that end) is passed in through OpenArgs.

Also, you could just use the overloads to set the date range in the where/filter parameters and pass the full date range into OpenArgs such as "January 1 - January 31" and assign it to the Label.Text for the label on the report