Date ranges showing on report

  • Thread starter Thread starter Bill McGill
  • Start date Start date
B

Bill McGill

I am using Access 2002 SP 2

I have a report which draws it's data from a query.
I have a date range prompt within the query:
"Between[Start Date]and[End Date]"

When I run the report I would like to have this date range
appear on the report.

Thank you.
 
Instead of letting the query prompt you, when you open the report have the report pop-up a
form to ask for those dates. Do this in the Form's Open event, before the record source is
read. Open the form with the acDialog window mode parameter to stop the code from running
until the form is closed or hidden. There would be 2 text boxes on the form (one for each
date), a label explaining what you want, and Ok and Cancel buttons. When you click Ok, you
will verify that correct data has been entered into the text boxes then hide the form
(Me.Visible = False). If Cancel is clicked, close the form. Have the next line of code in
the Open event check to see if the form is open (hidden is still open). If it isn't, then
you know that the user chose Cancel and you can Cancel the Open Event of the report as
well. If it is, then just let the report continue to open.

In the query, your Between statement will now refer to the 2 textboxes on the form for the
parameter.

Between Forms!frmMyPopup!txtStartDate And Forms!frmMyPopup!txtEndDate

Access will probably put brackets around the components above when you exit the box in the
query design grid.

Between [Forms]![frmMyPopup]!.....

When you exit the report, close the form.
 
PS. I forgot to mention this part. In the report, you can also get the values of the
textboxes on the form by referring to them the same way you do in the query. For example
you could have a textbox with a Control Source similar to this:

="Dates reported: " & Forms!frmMyPopup!txtStartDate & " to " & Forms!frmMyPopup!txtEndDate
 
Back
Top