Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2002
    Location
    UK near Blackpool
    Posts
    18

    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. #2
    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 09:49.
    Rod

    fe_rod@hotmail.com

  3. #3
    Join Date
    Jun 2002
    Location
    UK near Blackpool
    Posts
    18

    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. #4
    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. #5
    Join Date
    Jun 2002
    Location
    UK near Blackpool
    Posts
    18
    Will give it a go.

    Cheers Guys

  6. #6
    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.
    Rod

    fe_rod@hotmail.com

Posting Permissions

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