Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Dec 2000
    Location
    Nebraska
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access97 SR2 Table Summation Error (Access97 SR-2)

    A table of data in Access 97 is yielding incorrect results when the data is summed. The data comes from an AS/400 to this table via an Access query. The field in question is defined as currency in Access, and the individual amounts appear to be correct in the table. But the sum is off by .02 (i.e. when I add the table amounts on a calculator, it comes out to be the correct amount, .02 larger than the sum in Access). The values are all less than 9 digits with 2 decimal places. A query that sums the values as well as the Sum and DSum functions all yield the same results, .02 off. Using the function suggested by the Microsoft Knowledge Base (Q177360) for correcting rounding errors in large currency fields, I get the same result, .02 less than the sum of the values in the table.

    I

  2. #2
    Lounger
    Join Date
    Aug 2001
    Location
    Toronto, Ontario, Canada
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access97 SR2 Table Summation Error (Access97 SR-2)

    I read an article just recently about rounding issues in MSAccess - unfortunately it is at home and I cannot remember it's exact title. I know I found it on www.support.microsoft.com - and I got there because of a link someone posted on a bulletin board somewhere.

    I know this is vague help, but you might find the answer on microsoft site. Search for Rounding.

    HTH

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access97 SR2 Table Summation Error (Access97 SR-2)

    Floating point numbers are notorious for being imprecise. And currency format holds 4 decimal places, no matter how many you display, so that's where your rounding error is coming from. Unfortunately, Access 97 doesn't have a built-in rounding function the way Access 2000 has, but here's a routine a built several years ago for Access 97 that is similar to the Excel Fixed() function.
    <pre>Public Function FixedNum(ByVal dblNumber As Double, _
    ByVal intPlaces As Integer, _
    Optional blnRoundUp As Boolean = True) As Variant
    <font color=448800> ' Created by Charlotte Foust
    ' 3/5/99
    ' Similar to Excel Fixed() function
    ' Negative numbers rounded to left of decimal point
    '
    ' Modified 4/8/99 -- handle intPlaces=0
    ' Modified 7/31/2000 -- handle fewer decimal
    ' places than specified in intPlaces.
    ' Modified 3/26/2001 -- cleaned up code and added comments
    ' Modified 6/21/2001 -- corrected handling for whole numbers passed in
    ' Modified 9/21/2001 -- 7/31/2000 patch </font color=448800>
    Dim dblNum As Double <font color=448800> 'holds whole number left of decimal</font color=448800>
    Dim strDecimal As String <font color=448800> 'holds decimal portion of number _
    converted to string</font color=448800>
    Dim lngDecimal As Long <font color=448800> 'holds decimal converted from string _
    and rounded to specified places</font color=448800>
    Dim intDecPos As Integer <font color=448800> 'holds position of decimal point in _
    original string</font color=448800>
    Dim intRounder As Integer <font color=448800> 'holds the number that determines rounding</font color=448800> </font color=448800>
    Dim strNum As String <font color=448800> 'holds the double converted to string</font color=448800>
    Dim blnNegative As Boolean <font color=448800> 'holds true if number is negative</font color=448800>
    Dim blnTruncate As Boolean <font color=448800> 'holds true if intPlaces = 0</font color=448800>
    Dim dblReturn As Double <font color=448800> 'holds the return value of FixedNum</font color=448800>

    <font color=448800> 'set the Truncate and Negative flags</font color=448800>
    If intPlaces = 0 Then
    blnTruncate = True
    End If <font color=448800> 'intPlaces = 0</font color=448800>
    If dblNumber < 0 Then
    blnNegative = True
    End If <font color=448800> 'dblNumber < 0</font color=448800>

    <font color=448800> ' Extract the whole number
    ' from the double</font color=448800>
    dblNum = Fix(dblNumber)
    <font color=448800> ' convert the double to a string</font color=448800>
    strNum = CStr(dblNumber)
    <font color=448800> ' find the decimal point in the string</font color=448800>
    intDecPos = InStr(strNum, ".")

    <font color=448800> ' if intDecPos<>0 extract the decimal portion</font color=448800>
    If Not blnTruncate And intDecPos <> 0 Then
    <font color=448800> ' extract the decimal portion of the double</font color=448800>
    If intPlaces > Len(strNum) - intDecPos Then
    intPlaces = Len(strNum) - intDecPos
    End If <font color=448800> 'intPlaces > Len(strNum) - intDecPos</font color=448800>
    strDecimal = Right(strNum, Len(strNum) - intDecPos)
    lngDecimal = CLng(Left(strDecimal, intPlaces))
    Else<font color=448800> 'Not blnTruncate And intDecPos <> 0</font color=448800>
    strDecimal = "0"
    lngDecimal = CLng(strDecimal)
    End If <font color=448800> 'Not blnTruncate And intDecPos <> 0</font color=448800>

    <font color=448800> ' extract the rounding determinant from the decimal portion</font color=448800>
    If Len(strDecimal) >= intPlaces + 1 Then
    intRounder = CInt(Mid(strDecimal, intPlaces + 1, 1))
    Else <font color=448800> 'Len(strDecimal) >= intPlaces + 1</font color=448800>
    intRounder = 0
    End If <font color=448800> 'Len(strDecimal) >= intPlaces + 1</font color=448800>

    <font color=448800> ' apply rounding unless blnRoundup = False</font color=448800>
    Select Case blnRoundUp
    Case True <font color=448800> 'blnRoundUp</font color=448800>
    If intRounder >= 5 Then
    lngDecimal = lngDecimal + 1
    End If <font color=448800> 'intRounder >= 5</font color=448800>
    Case Else<font color=448800> 'blnRoundUp</font color=448800>
    End Select <font color=448800> 'Case blnRoundUp</font color=448800>

    <font color=448800> ' If intPlaces <> 0, add the decimal
    ' portion back to the base number</font color=448800>
    If Not blnTruncate Then
    <font color=448800> ' if the number is negative,
    ' subtract the decimal amount</font color=448800>
    If blnNegative Then
    dblReturn = dblNum - (lngDecimal / 10 ^ intPlaces)
    Else <font color=448800> 'blnNegative</font color=448800>
    dblReturn = dblNum + (lngDecimal / 10 ^ intPlaces)
    End If <font color=448800> 'blnNegative</font color=448800>
    Else <font color=448800> ' Not blnTruncate</font color=448800>
    Select Case blnRoundUp
    Case True <font color=448800> 'blnRoundUp</font color=448800>
    <font color=448800> 'apply rounding</font color=448800>
    If CInt(Left(strDecimal, 1)) >= 5 Then
    If blnNegative Then
    dblReturn = dblNum - 1
    Else <font color=448800> 'blnNegative</font color=448800>
    dblReturn = dblNum + 1
    End If <font color=448800> 'blnNegative</font color=448800>
    Else <font color=448800> 'CInt(Left(strDecimal, 1)) >= 5</font color=448800>
    dblReturn = dblNum
    End If <font color=448800> 'CInt(Left(strDecimal, 1)) >= 5</font color=448800>
    End Select <font color=448800> 'Case blnRoundUp</font color=448800>

    <font color=448800> 'set the return value</font color=448800>
    dblReturn = dblNum
    End If <font color=448800> ' Not blnTruncate</font color=448800>

    FixedNum = dblReturn
    End Function <font color=448800> 'FixedNum(ByVal dblNumber, _
    ByVal intPlaces, _
    Optional blnRoundUp) As Variant
    </font color=448800> </pre>

    Charlotte

  4. #4
    Lounger
    Join Date
    Dec 2000
    Location
    Nebraska
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access97 SR2 Table Summation Error (Access97 SR-2)

    Thanks, Donna.

    Paul

  5. #5
    Lounger
    Join Date
    Dec 2000
    Location
    Nebraska
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access97 SR2 Table Summation Error (Access97 SR-2)

    Thanks Charlotte. That looks like the answer, but I still have some questions.

    I

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access97 SR2 Table Summation Error (Access97 SR-2)

    The roundup is optional and has a default value that applies if you don't override the argument. Your type mismatch may be a coersian issue since the function trys to return a variant. Try changing the function's return value from variant to double or even currency and see if that eliminates the error. Frankly, I haven't used this in so long, I'm not sure why I used a variant in the first place.

    Most of your stuff is probably OK using a currency datatype, although you may need to test that, but when you do things like averaging or perform any multiplication or division, you'll definitely have to watch out for those extra decimal places. That's when you probably want to use the FixedNum function.

    It might be simplest to first use the CCur() function in your original query to append the AS/400 data to an Access table, if you aren't already doing so. Then check the data in the table to see if you're getting something other than zeroes in the last two decimal places. That will tell you whether the problem is coming in with the data or is in the way doubles behave. If it's coming in with the data, then either use the FixedNum function in the original append query or use it in an update query on the Access table to get rid of any values in the third and fourth decimal places. After that, you'll only have to worry about using it in mathmatical calculations to avoid rounding errors.
    Charlotte

  7. #7
    Lounger
    Join Date
    Dec 2000
    Location
    Nebraska
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access97 SR2 Table Summation Error (Access97 SR-2)

    Once again, thanks for your help, Charlotte.

    This morning I've been doing what I should have done last week, reading previous postings on the problem. I just don't do Access enough to remember many of the things I should. But your patience and help have been wonderful.

    I promise to read first next time. But please don't go away.

    Paul

Posting Permissions

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