Passing Date Range Parameter to Multiple Subreports (1 Viewer)

[LBinGA]

Hello all:

I am trying to pass a date range parameter & an additional parameter (Type of Audit) to 6 subreports based off individual crosstab queries and housed on one Unbound Report ("rptFinal").

I have an Unbound Form "frmDate" passing a date range and Type of Audit using [Start Date] and [End Date] to rptFinal with a button that simply opens the rptFinal as follows:
stDocName = "rptFinal"
DoCmd.OpenReport stDocName, acViewReport

In each Crosstab query, I have set the parameter criteria (in both the Parameter section & the query itself) to:
[Forms]![FrmDate]![Start Date] And [Forms]![FrmDate]![End Date]
and also,
[Forms]![FrmDate]![Enter Type of Audit]

On each subreports On Load Event, I have added:
Private Sub Report_Load()
Me.Filter = "[DteAuditDate] BETWEEN #" & Forms!frmDate![Start Date] & "# AND #" & Forms!frmDate![End Date] & "#"
Me.Filter = "[Type of Audit] = #" & Forms!frmDate![Enter Type of Audit] & "#"
End Sub

and I've set the Filter On Load property to: Yes

I can open frmDate, fill in the date and Type of Audit, launch the report and it runs with no error, however, I have 6 blank subreports in report Preview. The headers are showing up but none of the data. I don't know where I've gone wrong.

Can anyone help? This is the last piece of a huge project I'm working on.

Thanks in advance,

LB in GA

 

[LBinGA]

The SQL view of the crosstab queries running each subreport is as follows with the exception of the SELECT, is identical:

PARAMETERS [Forms]![FrmDate]![Start Date] DateTime, [Forms]![FrmDate]![End Date] DateTime, [Forms]![FrmDate]![Enter Type of Audit] Text ( 255 );
SELECT qryForm.txtTypeOfInsurance, Count(qryForm.txtTypeOfInsurance) AS CountOftxtTypeOfInsurance
FROM qryForm
WHERE (((qryForm.dteAuditDate)=[Forms]![FrmDate]![Start Date] And (qryForm.dteAuditDate)=[Forms]![FrmDate]![End Date]) AND ((qryForm.[Type of Audit])=[Forms]![FrmDate]![Enter Type of Audit]))
GROUP BY qryForm.txtTypeOfInsurance;

Thank you...I'm rather desperate at this point.
LB in GA

 

[LBinGA]

[SOLVED] Re: Passing Date Range Parameter to Multiple Subreports

Well, messing around with it for a little longer made me realize that I was asking for the criteria to meet two dates and not asking for BETWEEN two dates. The SQL should have read as follows:

PARAMETERS [Forms]![FrmDate]![Start Date] DateTime, [Forms]![FrmDate]![End Date] DateTime, [Forms]![FrmDate]![Enter Type of Audit] Text ( 255 );
SELECT qryForm.txtTypeOfInsurance, Count(qryForm.txtTypeOfInsurance) AS CountOftxtTypeOfInsurance
FROM qryForm
WHERE (((qryForm.dteAuditDate) Between Forms!FrmDate![Start Date] And Forms!FrmDate![End Date]) And ((qryForm.[Type of Audit])=Forms!FrmDate![Enter Type of Audit]))
GROUP BY qryForm.txtTypeOfInsurance;

So, now it works like a charm and I am off to go soak my head in the hot tub.

Thanks to anyone who considered my dilemma.

LB in GA