0

I have a base Query that pulls in data to be used in multiple queries.

Select
ClientActivities.FacilityID
,Facility.FacilityName
,ClientActivities.ClientID
,ClientActivities.ActivityID
,ClientActivities.ActivityDate
From
ClientActivities
Inner Join
Facility
on
ClientActivities.FacilityID = FAcility.FacilityID
Where
ClientActivities.ActivityDate Between [StartDate] and [EndDate]

This feeds two other Queries.

Select
FacilityName
,Count(ClientID) 
From
BaseQuery

and

Select
ActivityID
,Count(ClientID)
From
BaseQuery

When I put them both on a single report as subreports- it asks me for the StartDate and EndDate twice. I would like for it to ask only once. Any suggestions on how this can be done? While keeping it simple as once I turn this over to the user I will be leaving and the extent of their Access training is one college class.

Thanks,

Improve this question

1 Answer 1

Reset to default
1

Set up a form to run the report from...

  1. Create a form named "frmReports" that contains two text boxes and a button.
  2. Name the text boxes "txtStartDate" and "txtEndDate".
  3. In the queries, put the fully qualified names of the text boxes on the form into the criteria section of the queries. For example: Forms![frmReports]![txtStartDate] and Forms![frmReports]![txtEndDate]

  4. Behind the button click event, place the following code...

    DoCmd.OpenReport "ReportName"

To run the report...

  1. Open the form "frmReports".
  2. Enter the beginning and ending dates into the text boxes.
  3. Click the button you created.
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.