Access 2000 Report filter via VBA

[lynn_victoria]

I have an aggregate query that is running a report.
Access 2000
WinXP Pro

I have these fields in my report:
registrationfee
paymentamount

I want to total the registrationfees which i did like this:
=Sum([RegistrationFee])
I want to total the paymentamounts which I did like this:
=Sum(Nz([SumOfPaymentAmount],0))

I then want a TotalBalanceDue which I did like this:
=Sum(Nz([SumOfPaymentAmount]-[RegistrationFee],0))

However, the TotalBalanceDue field shows all balances including zero (or paid) and I would like to know if I can use VBA to filter the TotalBalanceDue field for <>0 at the Report level. Which would leave me with people i need to bill (>0) AND people I need to refund (<0)
If so, How?
Thanks
Victoria

 

[Pat Hartman]

You are not applying the Nz() to the correct field.

=Sum(Nz([SumOfPaymentAmount],0)-[RegistrationFee])

You were applying it after the subtraction which is too late. If one of the fields involved in the subtraction was null, the result would be null.

You can only filter on fields in the recordsource of a query. Since the total does not exist until you calculate it in the report, you cannot filter on it.

You can calculate the total in a query and add a where clause that eliminates all zero values. Then use the recordset created by that query to join with the query currently used as the recordsource for the report. This would have the result of filtering the report.

 

[lynn_victoria]

Access Report filter via VBA

Thanks Paul,
I agree with the Nz thing and it dawned on me this morning (ahmm 5am)

Howwever as to the query part, you totally lost me. I am having trouble because some people have multiple payments on one purchase so that's why the aggregate query to start with, I sum on the Payment amount field.

If you would like to provide a little bit more information, that would be great. I can "get it" with some small examples, but could you clarify a bit.

Thanks,
victoria

 

[Pat Hartman]

query1:
Select CustID, Sum(Amount) As SumOfAmt
from YourTable
Group By CustID;

query2:
Select t.*
From YourPresentQuery As t Inner Join query1 As q ON t.CustID = q.CustID
Where q.SumOfAmt <> 0;


The first query sums the amounts for a customer. The second query joins it to your original query. This will prevent your original query from returning rows where the amount field summed to 0.