how do I enter query criteria only once? 1

[mattock7]

I created a report with 6 sub-reports. Each sub report pulls from a different query, but each of these queries draw from one main query, which prompts for start/end date criteria. I want to use the same start/end date criteria for all of the sub-reports.
When I open the report, it askes for the date criteria 6 times, running the main query each time. How can I fix this so that it only asks for the criteria, and runs the main query once?

 

[genomon]

Try basing ALL the reports ( sub & main) on a single query containing fields needed by everything.

 

[mattock7]

The problem is, i need to summarize the information in the same field several different ways. The only way I could figure out how to do so was with seperate queries. Is it possible to summerize the same field in different ways within the same query?

 

[Imhoteb]

Hi,

You could enter the date in a textfield in a form.
Then you could use this textfield as criteria in your query.
This should solve the problem.
the criteria for the date in your query should look like this

[Forms]![myForm]![Textbox1]

Good luck

 

[mattock7]

That sounded like a great idea, and I did create a form that automatically pops up when I run the report. It allows me to enter start and end date info, and the queries on the report are pointed at the form for their criteria.
However, when I run the report, it still prompts for each start and end date even though I just filled it out on the popup form.
Any ideas?

 

[raskew]

Assuming that each query has StartDate and EndDate fields, with [Enter Start Date] and [Enter End Date] criteria -- (if you have variations, you need to make them all the same).

(1) Go to each query in turn.

(2) In query design mode, click on the toolbar Query | Parameters.

(3) Add to each:

- Enter Start Date (no brackets or quotes), Date/Time
- Enter End Date (no brackets or quotes), Date/Time

(4) Rerun your application. With luck, you'll be prompted only one time for each parameter.

Please post back with your results.

 

[mattock7]

Sorry Raskew,
I still get prompted seperately for each query.

I did find something interesting, though, when experimenting with Imhoteb's recomendation- After entering the criteria once into a form- I still get prompted for each criteria in each query- however: if I leave the subsequent prompts blank, the queries use the criteria entered on the form.
So, that is a partial solution, I only have to enter the data once, but I still have to go through the pop-up prompts for each criteria- I just have to leave them blank.

Anyone have any ideas on stopping the prompts?

 

[Imhoteb]

Hi,

Are you sure the form you put the date in is not closed when you call the other query's.
When it is the query can't find the criteria anymore.
If it isn't closed I don't see the problem

Good luck

 

[mikehoot]

Did you enter the query parameters in Menubar? If so delete these.

 

[mattock7]

After clearing the parameters for each query, and making sure the grouping levels didn't have any date references, And, changing one of the fields from Report # to Report Number (I guess Access doesn't like the # sign) I finaly got the bloody thing to work!
Thanks everyone, especially Imhoteb, for the advice.