Designing a Report

[Guest]

I have created several queries that summarize the data from a table. But now
I want to be able to display this information in a report. I want users to
have the ability to enter a date range (2/1/07-2/21/07 or Feb 2007) and then
have my queries execute and display the resulting information. Right now a
date has to be entered for each query but I only want the user to enter the
date range once and have each query execute to create the report.

What’s the best way to do this?

Thanks.

 

[Jeff Boyce]

Matt

Create a new form. Add "Start Date" and "End Date" controls.

Modify your query (queries) to point to the form for the date range values,
instead of using the parameter/prompt in the query.

Base your report(s) on your query(ies).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Jeff,
I have created the form. But how do you get the queries to refer back to the
form for input data?

Thanks.

 

[Jeff Boyce]

Open the query in design view.

In the Selection/Criterion "cell", put something like:
Between Forms!YourNewForm!txtYourStartDateControlName And ...

Take a look in Access HELP re: expressions.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Jeff,
I have been trying this since last night. When I put in the expression you
listed below the query prompts for the dates to be entered even though they
have already been entered in the form.

I did get it to run by having the form refer to a macro that I build. I have
the macro set to execute the queries when called.

However, my original problem continues. I still need to get the results of
about seven queries into a report. I've tried running the report wizard but
it won't let me pick fields from multiple queries. The error message that I
get is

You have chosen fields from record sources which the wizard can't connect.
You may have chosen fields from a table and from a query based on that table.
If so, choosing fields from only the table or only the query.

 

[Jeff Boyce]

Matt

If the form is not open when the query tries to run, it can't "see" the
controls.

If the form is open and doesn't have any values in the controls, the query
uses THOSE (not) values.

If the query is still prompting you for dates, is it using the old parameter
prompts, or the new "Forms!..." prompts?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/