# Thread: rounding dollars during calculations

1. ## 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. ## 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.

3. ## Re: rounding dollars during calculations

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

4. ## 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>

5. ## 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. ## 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.

#### Posting Permissions

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