# Thread: Currency calculation not correct

1. Registered User
Join Date
Jun 2002
Location
UK near Blackpool
Posts
19

## Unanswered: Currency calculation not correct

I have set up a db to produce invoices for customers. The fields at the bottom of the invoice display:-

subtotal
Vat = [subtotal]*[vatrate]/100
Total = [subtotal]+[Vat]

This works on most invoices ok but on some it prouduces errors due to rounding. ie a £69 subtotal gives Vat of 12.07 (based on UK 17.5%) then adds them as £81.08 ie .01p more than the correct amount.

It is obviously due to the Vat calculation being 12.075 and access rounding the total up.

How do I rectify this both for the report and my screen display.
I think I may have seen this problem with vat calculations before but cannot find the relevent article.

I have on a previous posting been sent to sites with custom rounding (bankers rounding) but to date this hasnt worked correctly.

using Access97

Any help appreciated.

2. Registered User
Join Date
Jun 2002
Location
Saudi Arabia / Philippines
Posts
126
Hi Dave,

There is a ROUND function that you can use but the following explanation of how it works might surprise you.

If the digit to be rounded is 0 through 4 then round down to the lower number.
If the digit to be rounded is 6 through 9 then round up to the higher number.
If the digit is exactly 5 round up if the digit to the left is odd otherwise round down.

Hence in your case 12.075 is rounded up to 12.08 but 12.085 is rounded down to 12.08

Now it depends upon what you want. You can get up to all sorts of arithmetic tricks to produce variations on the above rules but I guess at the end of the day it's what C&E say about VAT calculations. It is a long time since I worked in the UK so I'm out of touch.

However there are a couple of basic principles that help to eliminate some of the rounding anomalies. First round each amount as early in the process as possible. Then when you add them up you are certain that your total is the true sum. Second, avoid using Double data type for these situations as it is impossible to store some numbers exactly in this type. Use Currency instead.
Last edited by Rod; 10-11-04 at 08:49.

3. Registered User
Join Date
Jun 2002
Location
UK near Blackpool
Posts
19

## Re currency

Thanks Rod, yes looked at round function before. Looks like I am going to have to get my head round righting a custom function for this one. Dont know if Access makes it any easier in later versions - its a pain not being able to drop curency boxes onto a report and add up correctly. VAT man wants either rounding down or up but must be consistent. Cheers.

4. Registered User
Join Date
Oct 2004
Posts
8
try changing the formula for VAT to :
Code:
`(Int(([subtotal] * ([vatrate] / 100) * 100))) / 100`
i haven't tested it extensively, but it seems to work

5. Registered User
Join Date
Jun 2002
Location
UK near Blackpool
Posts
19
Will give it a go.

Cheers Guys

6. Registered User
Join Date
Jun 2002
Location
Saudi Arabia / Philippines
Posts
126
Dave,

So the VAT man only wants consistency. OK, we can give him that. Simply change your coding to

Vat = Round([subtotal]*[vatrate]/100,2)
Total = [subtotal]+[Vat]

and all will be well. Make sure you do this everywhere the calculation is made.

Give my regards to the Fylde. My father spent his last years living in Thorton and I have many happy memories of the area.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•