Hide Rows in a subreport

[gemadan96]

I have a report called Account with two subreports, Charges and Payments.

There are some records in Charges that have a 0 value because they've been waived. The record needs to be in the table, but doesn't need to be included in the subreport. I would like to hide the rows where the charge is 0. One way to accomplish this is in the query have it only retrieves the records where the charge is >0. If the account has all 0 values for the charges then no records appear. The problem is that I want to sum the charges and compare them with the payments to determine the balance. When there are no charges in the Charges subreport the balance control displays as #Type!

So I would like to either hide the rows that have a charge of 0 or if there are no charges then the Sum of the Charges should = 0.

Any suggestions on how I can do this?



I have used conditional formatting to change the color of the 0 value to white, but I would rather just not see the row in the report.

[pbaldy]

This should work for option 2:

nnz function

You could use code in the detail format event to hide lines, but my first inclination is the above.

[gemadan96]

An update: In the report I've built an expression for a control to calculate the balance using the following -

=Nz([Charges subreport].[Report]![TotalCharges])-Nz([Payments subreport].[Report]![TotalPayments])

This works in Report view but not in Print Preview. In print preview or when I print the control prints as #Size!

[pbaldy]

You didn't want to try the function I suggested?

[gemadan96]

I think I was typing my update as your reply came in.

I tried NNZ and it works perfectly.

Thanks.

[pbaldy]

Happy to help.