I have one main report.
Inside that report I have about 7 subreports. All doing their own thing from their own queries. None of these sub report queries are by date range at the current point in time. They all query by a 'group' field. I have used all the subreports because I want different values for some than others and also I want to also calculate my own totals from those reports in other places.
This main report and all of the subreports work 100% and give me the correct answers and layout I want, so long as they are using ALL the data in the underlying tables etc.
However, Is there a way to code the MAIN FORM so that when it is opened, the user is asked for a 'Start Date' and a 'Finish Date' and those two dates will then become an additional criteria for each subreport that needs data by date. I assume that if I was to give each subform criteria by date then a user will need to enter it 7 times!!!!!!. Am I correct? Or, am I trying to do this job totally the wrong way.
Can someone please give me some guidance or assistance on this problem.
I have done something similar in forms, where the main form had a series of combo boxes which provided parameters. The source for each of the subforms was a query which referenced the appropriate combo boxes in the main form. You can use the Query Builder or straight SQL .
The clause comes out looking like:
WHERE DepartmentKey = Forms!frmCompany!cmbDepartmentKey
I don't see why something similar should not work in reports. You could actually create a form which accepts the parameters and which contains a button to launch the report. So long as the form remains open, the full object name for the combo box will provide an acceptable reference n the subreport query.
Thanks very much guys. I have tried what you suggested (coz you are experts...lol) but still missing the obvious I guess.
I created a form from a query.....and yes the info on that form is A OK and yes, I can select the data by date as I want. I tried giving the subreport the source of the form but then it started to ask for the date range again so I took it out.
I will give made up examples of four rows of that form.
4000 Bottles Direct Income $4000.00
4100 Rags Direct Income $200.00
6000 Wages Direct EXPENSE $1000.00
6100 Power Direct EXPENSE $100.00
I am trying o hook the subreport to the form but I ALWAYS only ever get ONE of the above lines. It seems to be only showing the data line which is highlighted by the cursor.
What I really am trying to do is shift ALL the Direct Income to one subreport - line by line exactly as above - and ALL the Direct Expenses to another subreport line by line. On some occassions there might only be one to shift or there might be 10 to shift so I cannot really just do them line by line because every user will be different.
I am using the following code right now which only will shift one row at a time.
Can someone please show me my error in the above code.