SubReports

[Waynest]

Hi

I have a pay report which accepts two input parameters - a 'from date' & a 'to date'. This works fine, the first part of the report shows the employee's particulars and the date range under scrutiny.

I have added a subreport which shows a series of figures broken down per day which is linked to the main report by employee no. I am having problems making the subreport limit the information it shows to the date range shown on the main form. I'm thinking I need to be able to modify the SELECT statement in the RecordSource property of the subreport with the input parameters from the main report eg

SELECT * FROM Pay WHERE PayDate > ? AND PayDate < ?

Its the ? bit I'm having trouble with, how do I get the dates into the query?! Anyone know how to do this or am I using the subreport incorrectly?

 

[VasDev]

2 ways

Before your report opens you can have a form open (say frmDatesForReport) with two unbound text boxes that would store these two dates say txtDate1 and txtDate2. After this information is filled in you would minimize this form and open the report and use
SELECT * FROM Pay WHERE PayDate > [Forms]![frmDatesForReport]![txtDate1] AND PayDate < [Forms]![frmDatesForReport]![txtDate2]

OR

The other option would be set your SQL statement to
SELECT * FROM Pay WHERE PayDate > [Please input a start date] AND PayDate < [Please input an end date]
This will prompt the user for this information.

MVas

 

[Waynest]

Sorry, I forgot to mention I'm using access 2000 with SQL server and the query designer wont accept that syntax for referencing fields on forms. I'm still stuck

 

[WilsonM]

Did you try passing the SQL to a string variable and then set RecordSource. Whatch out for the syntax of SQL Server, I can't help you with this one but I know that in some cases you need to use some caracters on the where (#).

Dim str as string

str = &quot;SELECT * FROM Pay WHERE PayDate > #&quot; &amp; [Forms]![frmDatesForReport]![txtDate1] &amp; &quot;# AND PayDate < #&quot; &amp; [Forms]![frmDatesForReport]![txtDate2] &amp; &quot;#;&quot;

subReport.RecordSource = str

I hope I could help,

Wilson

 

[Waynest]

I tried that earlier. I got a message saying I couldnt change the property because the sub report wasnt open (even when the main report is open). So I tried opening the subform, changing the recordsource, and then closing it before opening the main form but it prompts if you want save changes, which I cant really live with. Any more suggestions, this thing is driving me nuts?

 

[ZaZa]

Hi Waynest or Wilson,

Did any of you guys fuigure this one out? or anyonbe out there, for that matter? I have a similar situation, The difference is that on my main form I have an option group and I need to requery the subreport's recordscource based on the value in the option group.

My code goes something like:
---------------------------------------
Select Case Me![grpOptions].Value
Case 1
strCriteria = &quot;Select Top 3 balh blah blah..&quot;
Reports![MainRpttName]![SubreportName].RecordSource = strCriteria
Case 2
Blah, Blah blah
End Select
-----------------------------------------

But it keeps telling me that the report referred to is unknown or something like that.

I'm a bit stuck so any help that any one can give will be greatly appreciated.

Thanks,
ZaZa