I have 2 reports that both use the same subreport. The linking filed is
MemHeadOfHouseID. They are RptPaymentRecord & RptPaymentRecordUnPaid.

The query for the sub is long and irrelevant, and contains user defined
functions which slow it down. It runs much faster if I filter it on
MemHeadOfHouseID (only returns a few records instead of many hundred)

The filter I am trying to apply is:-

IIf(IsReportLoaded("RptPaymentRecord"),[Reports]![RptPaymentRecord]![MemHeadOfHouseID],IIf
(IsReportLoaded("RptPaymentRecordUnPaid"),[Reports]![RptPaymentRecordUnPaid]![MemHeadOfHouseID]))

The IsReportLoaded function just returns True or False

If I run the RptPaymentRecord report, I get asked for the parameter
[Reports]![RptPaymentRecordUnPaid]! [MemHeadOfHouseID] – the one I am not
needing If I just run the query, I get asked for both parameters.

What I want to achieve is that if neither report is open, there should be no
request for parameters, and all records should be displayed in the query.
If one or other report is open, only the records with the same
MemHeadOfHouseID should print, and of course, no request for the other
parameter.

I know I can get over it with 2 queries and 2 subreports to go with the 2
main reports, but feel that this should be unnecessary.
Ideas please

Thanks

Phil

On Nov 9, 11:40*am, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
Why not just call a function instead of all the IIFs()? Ex:
FieldToUse(Me.ReportName). Let the function return the processed and
correct value?

On 09/11/2011 18:34:33, Patrick Finucane wrote:
Good idea

Function WhichReport(ID As Long) As Long
' Called by query QRptSpaceBoat

If IsReportLoaded("RptPaymentRecord") Then
WhichReport = Reports!RptPaymentRecord!MemHeadOfHouseID
Exit Function
End If

If IsReportLoaded("RptPaymentRecordUnPaid") Then
WhichReport = Reports!RptPaymentRecordUnPaid!MemHeadOfHouseID
Exit Function
End If

WhichReport port = ID ' Neither report open, so return the MemHeadOfHuuseID

End Function

Works a treat

Thanks

Phil