date parameter prompts on reports

[Guest]

I have created two reports that are driven by queries showing active clients & active employees. I would like to have the report prompt you to enter the date parameters so that others in the office won't go into the original query and possibly erase the query or code behind it. this is to track sales leads and employee/applicant leads

If this is possible, would it then be possible to have the date parameter print on the report/page header

 

[Allen Browne]

Two approaches.

1. Parameter in query
In the Criteria row of the query that supplies data to your report, under
the date field, enter a name that doesn't match any field, e.g.:
Between [StartDate] And [EndDate]
Declare these parameters: Parameters on Query menu.
Two boxes will pop up asking for the 2 dates whenever you open the report.
You can refer to these parameters in the report, e.g. a text box with
ControlSource of:
=[StartDate]

2. A form where the user enters the dates.
For an example of how to do this, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
To refer to these values from the report, use a text box bound to:
=[Forms]![SomeForm]![StartDate]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have created two reports that are driven by queries showing active

clients & active employees. I would like to have the report prompt you to
enter the date parameters so that others in the office won't go into the
original query and possibly erase the query or code behind it. this is to
track sales leads and employee/applicant leads

If this is possible, would it then be possible to have the date parameter

print on the report/page header

 

[Guest]

WayWAy Cool!

That absolutely hit the spot, thanks for making me look good!

Couple of quick tweaking questions.

You said that I could use this form on all sorts of reports, I'm not familiar with Options groups or how to use a single form or list box to use the same form for more than one report. Or did I mis-interpret what you were referring to

 

[Allen Browne]

The toolbox contains a rectanglar thingy with "xyz" across the top.
It's an option group.
Put one on your form.
Then put option buttons inside it (from the toolbox).
Each option button gets a different value.

When you open the form and the user clicks on a button, the option group
(named Frame0 by default) gets the value of that button. If they click on
another option button, the first one is automatically deselected, and the
Value of the option group changes.

You can create an option group that lists the reports you want to show to
your user. Add command button to preview the report. User selects a report,
and clicks the preview button. It runs the code below to open the desired
report.

Private Sub cmdPreview_Click()
Dim strReport As String

Select Case Me.Frame0.Value
Case Me.Option1.OptionValue
strReport = "MyReport"
Case Me.Option3.OptionValue
strReport = "MyOtherReport"
'etc.
End Select

If Len(strReport) = 0 Then
MsgBox "What report was that?"
Else
DoCmd.OpenReport strReport, acViewPreview
End If
End Sub

Now you can also add code to the AfterUpdate event of the option group to
show or hide the appropriate boxes for the user to filter the reports. You
then build up a string like a WHERE to use as the WhereCondition of the
OpenReport action. This means that you are providing a huge range of
possible filtering options for the user to create reports based on a date
period, etc. It does involve some code, but it is incredibly flexible for
the users.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

WayWAy Cool!

That absolutely hit the spot, thanks for making me look good!

Couple of quick tweaking questions.

You said that I could use this form on all sorts of reports, I'm not

familiar with Options groups or how to use a single form or list box to use
the same form for more than one report. Or did I mis-interpret what you were
referring to