sub reports and date parameters 1

[rollox]

Hi

Hopefully someone can help with this problem. I have a report that contains 4 sub reports, based on a query (which includes a date field). The report has to be able to prompt the user for date parameters, i.e. between [date from] and [date to]. This works but I have to enter thae dates for each query behind the subreport.

I have tried to follow the advice from this thread: thread703-509843

But I cannot get it working. Ideally I would like users to enter one set of dates that would be applied to the sub reports, instead of 4 times.

If anyone has any ideas on how to resolve this problem I would be greatfull

Cheers

Rollox

PS I am new to access and this is probably really simple to achive but it is probably going to be the most complicated report to be run from the database.

 

[scriverb]

In following the recommendations of WildHare in the thread that you referenced all you have to do is use the form that you made to prompt for the dates for the form. Leaving the form open after you trigger the Print Preview your RecordSource query for the report will use these dates and each of the queries for the subReports will use the same Start and End Dates. You just have to make sure that all of the queries involved in this report and its subsequent subreports has as their criteria statements the references back to the original form.

Let me know if you don't understand this process.

Bob Scriver

 

[rollox]

scriverb

Thanks for the response. The part I cannot understand is ensuring that all the queries reference the form with the unbound text boxes txtStartDate and txtEndDate.

If it was one report based on a single query I would have Between [Date From] and [Date To] in the criteria of the date field. How do you change this to reference the unbound text fields in the form?

Also the main report with the 4 sub reports does not have a record source. Should it have? If required I can make one of the sub reports the main report and add the remaining 3 reports as sub reports?

If this reads like jibberish then please excuse me as it is early on Easter Sunday and this problem has been annoying be all weekend.

Any additional info you have would be very much appreciated.

Cheers

Rollox

 

[scriverb]

The Main Report MUST have a Record Source. There must be records that are read to initiate the main report to run. It just must have an entry in the Record Source property. Even if it is a Dummy Table with just one(1) record in it to be used to trigger this report that would be okay. Then your four subreports could stay as they are. Your Headers and Footers could remain. Just make a table with one record in it with just one field. Have that be your Record Source. Everything else can run off of that initial record.

The queries for the four subreports should all be modified in their criteria row to:
Between Forms![frmYourFormName]![txtDateFrom] and Forms![frmYourFormName]![txtDateTo]

You will have to update the red code. "frmYourFormName" is the name of your date prompting form. "txtDateFrom" and "txtDateTo" are the names of the controls on your form. Once you have done that you will have the report running off of one record for the main form which is just an initiator record and all four subreports and their queries will use the prompted date entries from your form to run their queries. Just one time will your User have to enter the dates.

On your form you should put a Date InputMask and Format so as to ensure that the User enters a legitimate formated date in the two text boxes.

Good luck with this and have a nice Easter Sunday.

Bob Scriver

 

[rollox]

Scriverb

Thanks for all the help.

I have the report working fine.

Happy Easter

Rollox