Report / Subreport with No Data - don't print Statement

[dhurwyn]

I have a report to do statements based on time period.

Report works fine, actually too good.

I have 500 clients but only 5 will have monies due during this period.

If I have a report with 2 subreports with no data I still get a page of
zero's on the main page, which is right but I want to suppress 0
balances and only print the statements for those with a balance. The
calculations of the subreports return a value to the main report and
shows balance due which works fine.Calculations occur on the subreports
which are not fired until after the onopen for the main report fires.

in the main report i tried playing with the onopen event with this:
Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
If Me.rptStatementAtAgencySubreport.Report.HasData = False And
Me.rptStatementCollAtClientSubreport.Report.HasData = False Then
Cancel = True 'Cancel opening of main report
End If
End Sub
I got an Error message when opening report runtime 2455

tried If Me!rptStatementAtAgencySubreport.Report.HasData = False And
Me!rptStatementCollAtClientSubreport.Report.HasData = False Then

another error message.

is the report_open event the wrong place?

tried If Me!rptStatementAtAgencySubreport.HasData = False And
Me!rptStatementCollAtClientSubreport.HasData = False Then runtime 438

Demi

 

[Marshall Barton]

I have a report to do statements based on time period.

Report works fine, actually too good.

I have 500 clients but only 5 will have monies due during this period.

If I have a report with 2 subreports with no data I still get a page of
zero's on the main page, which is right but I want to suppress 0
balances and only print the statements for those with a balance. The
calculations of the subreports return a value to the main report and
shows balance due which works fine.Calculations occur on the subreports
which are not fired until after the onopen for the main report fires.

in the main report i tried playing with the onopen event with this:
Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
If Me.rptStatementAtAgencySubreport.Report.HasData = False And
Me.rptStatementCollAtClientSubreport.Report.HasData = False Then
Cancel = True 'Cancel opening of main report
End If
End Sub
I got an Error message when opening report runtime 2455

tried If Me!rptStatementAtAgencySubreport.Report.HasData = False And
Me!rptStatementCollAtClientSubreport.Report.HasData = False Then

another error message.

is the report_open event the wrong place?

tried If Me!rptStatementAtAgencySubreport.HasData = False And
Me!rptStatementCollAtClientSubreport.HasData = False Then runtime 438

You need to calculate the balance in the report's record
source query so you can filter those statements out before
they get to the report.

I have no idea what the report's query looks like, but the
general approach would be something like this. First create
a query named qryBalance that will calculate the balance for
each client:

SELECT Clients.ClientID, Sum(Invoices.amount) As Balance
FROM Clients INNER JOIN Invoices
ON Clients.ID = Invoices.ClientID
WHERE Invoices.datedue < billdate
AND Invoices.Paid = False
GROUP BY Clients.Agency
HAVING Sum(Invoices.amount) <> 0

Then you can join that into the main report's record source:

SELECT Clients.*, qryBalance.Balance
FROM Clients INNER JOIN qryBalance
ON Clients.ID = qryBalance.ClientID