Show only amounts > 0.00

[ssatech]

I created a report in access 2003 to show Supplier Transaction Analysis. In one of my fields I have in the control source the following:

Name:SumBalance
Format: Euro
Decimal Places: 2

Control Source: =([OBTot]-([SumPurchase]+[SumBicInvoice]))+([SumPurchaseCredit]+[SumPayment])

Currently the report shows all amounts for this field, including amounts that have €0.00. What I want to do is have this report show the Sum Balance of this field with only amounts greater than €0.00 (e.g. €1.00).

Probably, need to use something like the IIF expression.

Any assistance greatly appreciated.

 

[Remou]

I suggest you use a query to do this calculation, that way you will have more control. The report can be based on the query. Is this what you mean?

 

[ssatech]

Thanks Remou,

But the report is already based on a query. This field is a calculated field I have in my ID footer section of the report.

The field in question is calculated based on 3 other fields I have on the detail section of the report using:

=([OBTot]-([SumPurchase]+[SumBicInvoice]))+([SumPurchaseCredit]+[SumPayment])

I just need to show a list of amount values greater then €0.00 when the report is open. At the moment the report includes value (e.g.)

Supplier Sum Balance
Corporation Tax €1501.00
Eris Ltd €0.00
Accruals €12.00
Costing €0.00


I need this:

Supplier Sum Balance
Corporation Tax €1501.00
Accruals €12.00


Thanks,

 

[Remou]

It may still be easier to use a subform and query in the footer. Having the gaps close up can be a nuisance, but you can try:

[tt]=IIf(([OBTot]-([SumPurchase]+[SumBicInvoice]))+([SumPurchaseCredit]+[SumPayment])>0,"Costing " & Format(=([OBTot]-([SumPurchase]+[SumBicInvoice]))+([SumPurchaseCredit]+[SumPayment]), Currency), Null)[/tt]

You will need to keep the boxes close together, or use a single box for all the totals. You will also need to check that you have set the Can Shrink and Can Grow properties.

 

[ssatech]

Thanks Remou,

I am getting a invalid syntax expression error.
"You have entered a comma without a preceding value or identifier

 

[Remou]

Oops:

[tt]=IIf(([OBTot]-([SumPurchase]+[SumBicInvoice]))+([SumPurchaseCredit]+[SumPayment])>0,"Costing " & Format(([OBTot]-([SumPurchase]+[SumBicInvoice]))+([SumPurchaseCredit]+[SumPayment]), "Currency"), Null)[/tt]

 

[ssatech]

Thanks Remou,
I decided to use your suggestion using a subform with query as the method I was trying to do was not working even with added expression you suggested above.

Thanks again for your help and direction...