report filter for formula

[bbznyc]

I have a report with an outbound box [TotalOwed]=[InitialBalance]+[Commission]-[SumOfPayments1]. I need to be able to open this report only with records of values of [TotalOwed] > 0. How should i do it?

All help appreciated. Thanks.

 

[Andrew Fergus]

Hi,

Is your report based on a table or a query? Also, does [TotalOwed] only exist on the report or is it part of the underlying table / query? Lastly, are the other three variables also sourced from a table and if so, are the default values set to zero? (as opposed to Null)

Questions aside, you might be able to apply the filter in the report design view : Edit -> Select Report -> View -> Properties -> Filter = [TotalOwed] >0 -> Filter On = Yes -> Save and Run. If [TotalOwed] only exists on the report then the filter may have to be something like this instead : [InitialBalance]+[Commission]-[SumOfPayments1] > 0

Alternatively, if [TotalOwed] is sourced from a query then you could set the criteria for [TotalOwed] in the underlying query to either <>0 or >0.

HTH, Andrew.

 

[bbznyc]

thanks for the replies to my previous threads.

1. In the previous report i discovered that my table relationships in the queiry were not correct

2. As in this case the report is solely based on the query and [TotalOwed] exists only on the report. [InitialBalance] and [Commission] are query based and [SumOfPayments] is query based. I will try to do [InitialBalance]+[Commission]-[SumOfPayments1] > 0 because [TotalOwed] is report based only.

Thanks for the possibilities as I tried all of them except the one top. I never knew it could be done like that. I will try it tonite. Thanks man.

 

[Andrew Fergus]

Hi, given you calculate most of your variables in the query then you should also be able to create a new field (i.e. TotalOwed), calculate the value for it and then set the criteria as >0. This way only those records with a TotalOwed value greater than zero will get passed through to the report.

Andrew.

 

[bbznyc]

sweet gods... works like a charm. Thanks Andrew!