Calculations in reports?

[glavster]

In my report I calculate VAT for Invoices In and for Invoices Out with this formula.

=Nz(Sum([AmountExVAT]*[VRate]),0)

There is an InOut Header and the calculation is in the InOut Footer

In the Report Footer how do I calculate the differnece between Invoices In and Invoices Out. And if the In value is higher have a + value and if the Out Value is higher have a - value.

Thanks

 

[Pampers]

Hi glavster,
Note there is a special MS Access Report section. For now. Give the Calculated Fields proper names, and in an 'unbound' field, do the calculation. In the recorsource of this field you could put something like: =Nz([InvoiceOut])-Nz([InvoiceIn))



Pampers
Keeping it simple can be complicated

 

[glavster]

Thanks for that Pampers but it is a little more difficult than that.
=Nz(Sum([AmountExVAT]*[VRate]),0) is a report calculation that appears once for Invoices In and once for Invoices out in the InOut fotter of the report.
So unless I can name text fields based on the value InOut value then I can make a calculation like =Nz([InvoiceOut])-Nz([InvoiceIn))

InvoiceInOutID = 1 for In
InvoiceInOutID = 2 for Out

It needs to be something like =Nz(Sum([AmountExVAT]*[VRate]),0) where InvoiceInOutID =1 Minus =Nz(Sum([AmountExVAT]*[VRate]),0) where InvoiceInOutID =2

Thanks

 

[PHV]

Like this ?
=Nz(Sum([AmountExVAT]*[VRate]*IIf([InvoiceInOutID]=2,-1,1)),0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[glavster]

Thanks PVH, thats great I'll give it a shot now.

Paul