# Thread: Can anyone help me fixing counting VAT problem?

1. Registered User
Join Date
May 2006
Posts
386

## Unanswered: Can anyone help me fix counting VAT problem?

I have a very small problem with counting VAT (tax). Following is the situation:

The Decimal Places: on NetAmount field and VAT field are set as 'Auto' and it was previously Set as '2' and in both cases after counting different expenses it makes the NetAmount and then when I count the VAT on the NetAmount in the 'Detail' section of the Report for example on £23.00 it gives me the VAT @ 17.5% as £4.03 which is fine and on £28.16 which is of course made of a few expenses it gives me the VAT as £4.93 which means the total VAT for should be equal to £8.96 but when I Sum both the above VAT Totals in the ClientIDFooter it gives me the total as £8.95 which means £0.01 pence less which is not a big deal but when it comes to accountancy it causes problems.

I would be most grateful for any assistance.
Last edited by Emal; 12-06-08 at 08:36.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
have a look at this

3. Registered User
Join Date
May 2006
Posts
386
you making certain you are using a rounding function on the the data.. you need to make certain that the grand total is the same as the sum of its parts. if you are doing any calculations make certain that each element ROUND()s to the correct decimal place, and totals using thise values use the ROUND()ed value
Do you mean by ROUND()S and ROUND ()ed that the decimal place for all the related files which I calculate should be the same? If so, Yes I have set all related fields (amounts, currencies, expenses etc) to 2 and also to Auto and to 3, but none of them worked and the Grand Total is differnt then sum of its parts. If ROUND() is something else, I would be grateful if you could please guide me to the right direction.

This is how I have set the Control Source of the GrandVATTotal
=Sum([VAT])
and the Format like this
£#,##0.00;(\$#,##0.00)
I have attached an example of VAT calculation in which you can see the difference in GrandTotal calcultion.

Thank you so much.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
no thats not what you are doing at all, you are not rounding the the numbers at any stage.
you are setting a display format in the report.
unless you are explicitly using the round function (or similar functions) then irrespective of how you are displaying information the backing data is in more than two decimal places.

every time you do a calculation then you MUST use the round function to fix the values to the appropriate number of decimal places. so when you do your VAT calculation make certain you round() the calcualtion

the display format is irrelevant to the fundamental problem, it merely masks the problem

in the absence of a rounding rule both calculations are correct
line 1 &#163;23.00 VAT @17.5% = 4.025 with your display format it shows 4.03 (but the data is 4.025)
line 2 &#163;28.16 VAT @17.5% = 4.928 with your display format it shows 4.93 (but the data is 4.928)

4.928+4.025 = 8.953 with your display thats 8.95

what you should do is round the number to the desired number of places, that way round the number stored woill change, NOT just the display format

but the summ

5. Registered User
Join Date
May 2006
Posts
386
Sorry to be nuisance but do you mean that I should change the Decimal places for each field to 2 in the backend? if this is what you mean, I have already done that in the back end but it is still not working. If this is not what you mean by Round Function, then I would be most grateful if you could please advise how to get some information regarding the use of Round Fuction. I could not find this in the help menu. Thank you for your time!

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
NO
look at the help system.. there is a function called ROUND, which ROUNDs the specified value to the specified number of places.

eg
ItemVAT = round(itemcost*VATRate,2)

this should be used anytime you do a calculation in a report or form which may generate more decimal places than you need, especailly if you are using those values in a sum in the report.

7. Registered User
Join Date
May 2006
Posts
386
I am afraid there wasn't much explained about ROUND FUNCTION expcet two lines on when to use it but with no examples or at least clear explanation. Being very new to this function I couldn't even understand as to where and how to use this function on my report and form sepcially when raising an invoice (report). Thank you for your time and inputs, you have been very patient with me.

8. Registered User
Join Date
Sep 2006
Posts
265
Here's my solution:

You convert all your values to a (long) integer by multiplying them by 100 and then dividing them by 100. Along the lines of:

Vat = CLng(.[Sale Amt] * .[Sale Vat Rate] * 100) / 100

Simon

Join Date
Nov 2004
Location
out on a limb
Posts
13,692