Results 1 to 3 of 3

Thread: Rounding

  1. #1
    Join Date
    Apr 2009

    Unanswered: Rounding

    So we have an Access database that we use to submit orders to our accounting department. Our accounting department uses a database called MSG to create invoices.

    One problem we're having is that, when rounding currency, MSG seems to be completely retarded.

    Example #1

    Total: $50

    Tax Rate: 8.125%

    Tax: $4.0625

    MSG rounds that UP to $4.07.

    Example #2

    Total: $191.44

    Tax Rate: 8%

    Tax: $15.3152

    MSG rounds that DOWN to $15.31!

    Why would it round UP a 2 but round DOWN a 5?

    This makes it very difficult to reconcile the totals between our Access database and what's in MSG because things keep getting off by a penny.

    Now, I don't have any control over how MSG works. I only have control over how Access works. Does anyone know, based on either experience or looking at the examples above, how MSG is rounding? And how to emulate that in Access? I've tried Round, I've tried SalesTax = Int(-1000 * PreTax) / -1000... nothing!

    I accept the fact that MSG might just be plain configured wrong - and that's okay. I just need to force Access to be wrong the same way!

    Thanks for your help!


  2. #2
    Join Date
    Sep 2006
    I reckon they have got GT and LT signs wrong.

    Example 1 they have added .01c instead of doing nothing
    Example 2 they have deducted .01c instead of adding 1c


    IntGross = Pretax * TaxRate
    IntWhole = int(intGross)
    IntFraction = intGross - IntWhole

    if intFraction < 50 then
    intPretax = Pretax + .01
    intPretax = Pretax - .01
    End if

    I would suggest that you are more correct to work out the tax correctly and personally I would stick to my guns and tell your Accounting people that there Accounting system is inaccurate.


  3. #3
    Join Date
    Dec 2004
    Madison, WI
    I had the same rounding issue with currency type fields. What I did was change the field types to a Number type field (Real type on SQL Server and Number type field for MSAccess with it defined as Double versus Long Integer.) Then I controlled the actual rounding in the code. I didn't test it as an MSAccess field but I'm guessing you wouldn't have any issues as long as the Number field is Double.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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