Results 1 to 6 of 6

20010228, 16:12 #1
 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??

20010301, 04:04 #2
 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

20010306, 14:06 #3
 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

20010306, 14:48 #4
 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 usingyou 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

20010306, 15:11 #5
 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.

20010307, 05:50 #6
 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