Thanks Fred!
Also, how do I specify a "name" prompt? One of the other results in the
query are staff names - what would I need to enter in the Criteria field in
order to run a report by both one particular staff name, and the date range?
--Franklin
fredg said:
On Tue, 22 May 2007 07:34:01 -0700, Franklin wrote:
I am using Access 2007. I am trying to set criteria prompts when running a
report, which is linked to a query. I am specifically looking for the report
to prompt for a start and end date, and also to prompt for a name. Can't I
set this criteria to come up as prompts when the report is run? I have tried
using a control and setting the date range (i.e. = "Between " & [Enter Start
Date] & " And " & [Enter End Date] ) and also using this in the Query
criteria, but it didn't seem to filter the cases between the dates I
specified. Help!
--Franklin
If you are not using a form to enter the parameters in, then the
actual query criteria syntax (on the Date field) should be:
Between [Enter Start Date] And [Enter End Date]
You will be prompted to enter the dates when the query/report is run.
If you wish to display on the report the actual dates entered as
parameters, then in an unbound control on the report, write:
= "Between " & [Enter Start Date] & " And " & [Enter End Date]
The bracketed text in the control must be identical to the bracketed
text in the query criteria.
On the same row as the date parameter (in the Staff Name column)
write:
[Enter the Name]
However, I think you would be better off going about this a bit
differently, as more than one Staff member can have the same name.
How will you differentiate between John Smith (in Accounting) and John
Smith (in Sales)?
Here is a better method.
Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
EmployeeID field and the Employee Name field, as well as one other
field that will help identify the correct employee, i.e Department.
Name the Combo Box 'FindEmployee'.
Set it's Bound column to 1.
Set it's Column Count property to 3.
Set the Column Width property to 0";1";1"
Also add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"
Add a Command Button to the form.
Code the button's click event:
Me.Visible = False
Name this form 'ParamForm'.
In the Query (that is the Report's Record Source) [EmployeeID] field
criteria line write:
forms!ParamForm!FindEmployee
As criteria in the query date field write (on the same row as the
EmployeeID criteria):
Between forms!Paramform!StartDate and forms!ParamForm!EndDate
Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog
Code the report's Close event:
DoCmd.Close acForm, "ParamForm"
When ready to run the report, open the report.
The form will open and wait for the selection of the Employee and the
entry of the starting and ending dates wanted.
Click the Form's command button and then report will run.
When the report closes, it will close the form.
