Calculating Grouped Totals

[mpd]

I have a Report based on a Query. The Report runs off a parameter query for a Job Number. The Job Number is then grouped by the Invoices which make up the Job Number. In the detail area I have a VAT field which is unbound and simply calculates , the figures displayed appear anything from 0 to 3 decimal places. In the Invoice footer I have added the following =sum([Item_VAT]), the answer in here appears to be rounded automatically. In the REPORT footer I have added the same formula =sum([Item_VAT]) because I want to add up the value of all VAT figures. The problem is that the formula in the report footer is adding up the Item_VAT from the detail section, I want it to add up the the Item VAT totals which have been rounded up and displayed in the Invoice Footer because this is the figure displayed in the bank accounts...the rounded figure.

 

[Carmen]

For your textbox in the Report Footer, have you tried =Sum([TextboxInYourInvoiceFooter]) instead of =Sum([Item_VAT])?

 

[suepowell]

Hi,

I have had this problem as well in calculating vat then adding it up to give a total that looks correct, eg 0.02+0.02 = 0.04 not 0.05 .

The answer I have found is to format your amounts as currency, this will force them to be displayed as 2 DP. Unfortunately currency values are stored to 4 DP so you can get rounding errors when you add figures.

My answer to this was to multiply the value by 100, then take the interger value and divide by 100 again.

eg sum((int([item_vat]*100)/100)

I know this seems very odd but it does work and you know exactly what figures you are adding. I have assumed you are working in pounds and pence, if working in pence only just take the integer value.

I don't think you can sum calculated values in a report, you will have to use the same formula in the invoice and report footers.

Hope this makes sense and helps the problem

Sue