Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Feb 2001
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    rounding dollars during calculations

    I am doing a complex calculation with currency. I do a calculation on the first part and get an answer that is out to 10 decimal places. I calclate the second part and get an answer that is out to 10 decimal places. Now I subtract the two parts and the numbers rounds at this point. I need part 1 and part 2 to round before the subtraction. It gives me different answers according to where the rounding takes place.
    Can this be done??

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

    Re: rounding dollars during calculations

    If you're calculating currency, why carry it out to ten decimal places? You can force the numbers to use 4 decimal places using the CCur() function, which will also reduce the rounding creep introduced by floating point calculations (including addition and subtraction!).

    If you want to force rounding on ten decimal places, use the Round() function, which allows you to specify the number of decimal places to round to.
    Charlotte

  3. #3
    Lounger
    Join Date
    Feb 2001
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: rounding dollars during calculations

    Charlotte,
    Is the Round() function available in Access 97? I can't find it.
    Thanks

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

    Re: rounding dollars during calculations

    Not that I know of, although you could write a function to hijack it from Excel, I suppose. That's one of the problems with not posting the version of Access you're using--you get answers that don't apply. Here's a not very elegant , user beware, routine that might work for you or at least give you a starting point for your own routine:

    <pre>Public Function Fixed(ByVal dblNumber, ByVal intPlaces, Optional blnRoundUp) As String
    ' Created by Charlotte Foust
    ' 3/5/99
    ' Modified 4/8/99 to handle intPlaces=0
    ' Similar to Excel Fixed() function
    ' Negative numbers rounded to left of decimal point
    Dim dblNum As Double
    Dim strDecimal As String
    Dim intDecimal As Integer
    Dim intDecPos As Integer
    Dim intRounder As Integer
    Dim strNum As String
    Dim blnNegative As Boolean
    Dim blnTruncate As Boolean
    Dim dblReturn As Double

    If IsMissing(blnRoundUp) Then
    blnRoundUp = True
    End If
    If intPlaces = 0 Then
    blnTruncate = True
    End If
    If dblNumber < 0 Then
    blnNegative = True
    End If
    ' Extract the whole number
    dblNum = Fix(dblNumber)
    ' convert the double to a string
    strNum = CStr(dblNumber)
    ' find the decimal point in the string
    intDecPos = InStr(strNum, ".")
    If Not blnTruncate Then
    ' extract the decimal portion of the double
    strDecimal = Right(strNum, Len(strNum) - intDecPos)
    intDecimal = CInt(Left(strDecimal, intPlaces))
    Else
    strDecimal = "0"
    intDecimal = CInt(strDecimal)
    End If
    ' extract the rounding determinant from the decimal portion
    intRounder = CInt(Mid(strDecimal, intPlaces + 1, 1))
    ' apply rounding unless False passed
    Select Case blnRoundUp
    Case True
    If intRounder >= 5 Then
    intDecimal = intDecimal + 1
    End If
    Case Else
    End Select
    If Not blnTruncate Then
    ' intPlaces <> 0, so add the decimal
    ' portion back to the base number
    If blnNegative Then
    dblReturn = dblNum - (intDecimal / 10 ^ intPlaces)
    Else
    dblReturn = dblNum + (intDecimal / 10 ^ intPlaces)
    End If
    Else
    Select Case blnRoundUp
    Case True
    If CInt(Left(strDecimal, 1)) >= 5 Then
    If blnNegative Then
    dblReturn = dblNum - 1
    Else
    dblReturn = dblNum + 1
    End If
    Else
    dblReturn = dblNum
    End If
    Case Else
    End Select
    dblReturn = dblNum
    End If
    Fixed = dblReturn

    End Function</pre>

    Charlotte

  5. #5
    Lounger
    Join Date
    Feb 2001
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: rounding dollars during calculations

    Thank You but I found that by using a format it will force rounding. format{"#,##") It was so easy I was not even seeing it.
    Again Thanks for your help and I will be sure to post my Access version next time.

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

    Re: rounding dollars during calculations

    You might run into problems using Format, since it returns a string. The application will try to convert it, but watch out for it.
    Charlotte

Posting Permissions

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