Overall plan for dynamic Report design

[trout64]

I have an instance where I built a form/subform that lists several fields (pulled together by a query). Access 97 filtering allows for great sifting and sorting of the viewable data. (Up to this point my query based form with subform works adequately.)

Mow I want to be able to print reports based on the filtered and sorted data. I've read the subform's filter into a OpenReport command which works well, but I don't know how to use the OrderBy information to order the report data. I also need to be able to set the groupings at run-time, and I don't have a good feel for how I should do this.

I also have to add start date and stop date to the criteria -which can't be done by right-clicking and selecting "filter by selection".

I'm just not sure how to approach the problem. I answer bits and pieces but can't seem to put together a plan that goes from start to finish efficiently. I would really appreciate some advice on a plan of attack.


Thanks in advance for any advice or links to helpful sites.

 

[THWatson]

trout64
To get the Start and End date fixed first, you can do one of two things...

1. In the query that populates the report, but criteria in the date column...
Between [Enter Start Date] And [Enter End Date]

2. Better still, design a form which has a StartDate and and EndDate text box. Let's call them txtStart and txtEnd Then, in the criteria for the date columm in your query, put..
Between Forms!YourFormName!txtStart And Forms!YourFormName!txtEnd

Then you can set the report's sorting and grouping in the report itself. Or is that subject to change? If so, there are recent posts about setting the sorting and grouping levels at runtime.

Tom

 

[dhookom]

Tom has great advice in #2 regarding the filtering of dates.

Sorting and grouping can reliably done in code and the levels must be "predesigned" since you can't create grouping levels on an open or opening report. Allen Browne has code at

http://members.iinet.net.au/~allenbrowne/tips.html

that demonstrates how to set grouping levels at run-time.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[trout64]

Thanks for the help. The allen browne site was particulary helpful. I was able to apply one of techniques I found there. I'm still not content with the end report.