Currency Rounding
Allan Bach
I have calculated fields on a form that calculate the tax amount for a
school district. Problem occurs since currency data type has 4 decimals. Is
there a way to limit a currency value to 2 decimal places and have the real
value at 2 decimal places. For example, the amount $234.4567 would show as
234.46 and if used in a calculation, still be 234.46?
I have tried using a RoundAU function from the KB of Microsoft but it does
not help.
Any ideas????
Thanks,
Allan Bach
ab...@ptd.net
Andy Baron
Here is a function I wrote for the Numbers chapter in the VBA
Developers Handbook, published by Sybex. It allows you to specify the
number of decimal places to round to. An example of calling it would
be:
?dhRound(234.4567,2)
234.46
Function dhRound(dblNumber As Double, intDecimals _
As Integer) As Double
' Rounds a number to a specified number of decimal
' places. 0.5 is rounded up
' From "VBA Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 1997; Sybex, Inc. All rights reserved.
' In:
' dblNumber:
' The number to be rounded
' intDecimals:
' The number of decimal places to round to
' Out:
' Return Value:
' The rounded number
Dim dblFactor As Double
Dim dblTemp As Double
dblFactor = 10 ^ intDecimals
dblTemp = dblNumber * dblFactor + 0.5
dhRound = Int(dblTemp) / dblFactor
End Function
Hope that helps.
-- Andy
Mike Painter
It should be noted that if you let Access do the rounding in the latest
version it now follows a much older rule when dealing with 5. It looks at
the next digit and rounds up if even and down when odd.
This is never less precise and will tend towards greater precision for large
sets of numbers.
Andy Baron wrote in message <35263745...@207.68.144.14>...
Rick Wallace
I use the following function (round_to_pennies) when multiplying or dividing currency amounts that
could result in fractions of a cent.
Note that this function assumes the caller has checked the input argument to ensure it is a valid
numeric value.
[The function provided by Andy Baron does not work correctly for negative amounts (due to the use of
Int rather than Fix, and not adjusting the sign on the one-half offset).]
Function round_to_pennies(ByVal in_dollars As Variant) As Currency
On Error GoTo rtp_err
Dim pennies As Double
pennies = CDbl(in_dollars * 100#)
round_to_pennies = CCur(Fix(pennies + (Sgn(pennies) * 0.5))) / 100@
Exit Function
rtp_err:
round_to_pennies = 0@
MsgBox Error$, 16, "round_to_pennies"
Exit Function
End Function
Alden Streeter
This is just semantics but actually I think the automatic method used is to
always round so that the digit before the last digit that is 5 becomes even.
:)
Alden
Mike Painter wrote in message <6g5uv4$4fh$1...@newsfep3.sprintmail.com>...
Allan Bach
I appreciate the responses I've received. Though all were good, I finally
found a way to fix the problem with the following:
Option Compare Database
Option Explicit
Const Factor = 100
'=====================================================
' RoundAU and TruncAU are designed to be added to the
' AfterUpdate property on a form control.
'=====================================================
Function RoundAU(X As Control)
X = Int(X * Factor + 0.5) / Factor
End Function
Function TruncAU(X As Control)
X = Int(X * Factor) / Factor
End Function
'=====================================================
' RoundCC and TruncCC are designed to be used in
' expressions and calculated controls on forms and reports.
'=====================================================
Function RoundCC(X)
RoundCC = Int(X * Factor + 0.5) / Factor
End Function
Function TruncCC(X)
TruncCC = Int(X * Factor) / Factor
End Function
I've used the RoundCC(X) function on the calculated value and it uses only
two decimal places in the value and the calculation.
Thanks again.......
Allan Bach
ab...@ptd.net
Ken Getz
In article <uokPvp2...@uppssnewspub05.moswest.msn.net>,
ab...@ptd.net says...
> . For example, the amount $234.4567 would show as
> 234.46 and if used in a calculation, still be 234.46?
>
> not help.
>
>
function, modified from the one in "VBA Developer's Handbook" (Sybex),
which ALSO isn't quite right. I believe this one does work correctly.
This is a much more complex problem than any of us would have believed,
if you want it to work EVERY time (:>)
Function dhRound(dblNumber As Double, intDecimals _
As Integer) As Double
' Rounds a number to a specified number of decimal
' places. 0.5 is rounded up
' From "VBA Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 1997; Sybex, Inc. All rights reserved.
' In:
' dblNumber:
' The number to be rounded
' intDecimals:
' The number of decimal places to round to
' Out:
' Return Value:
' The rounded number
Dim dblFactor As Double
Dim dblTemp As Double
Dim intSign As Integer
intSign = Sgn(dblNumber)
dblFactor = 10 ^ intDecimals
dblTemp = Abs(dblNumber) * dblFactor + 0.5
dhRound = intSign * Int(dblTemp & "") / dblFactor
End Function
Paul Thornett
Mike Painter wrote in message <6g5uv4$4fh$1...@newsfep3.sprintmail.com>...
>It should be noted that if you let Access do the rounding in the latest
>version it now follows a much older rule when dealing with 5. It looks at
>the next digit and rounds up if even and down when odd.
>This is never less precise and will tend towards greater precision for
large
>sets of numbers.
Assuming one decimal place only is required, do you mean that 0.58 becomes
0.6, while 0.59 becomes 0.5?
Andy Baron
Good revision, Ken. And I guess that even that should be improved to
include a parameter that specifies how .5 should be handled, with code
that implements rounding up/down for even/odd digits before the 5.
-- Andy
Alden Streeter
I think that was an error. The actual method is:
0.575 becomes 0.58, 0.585 becomes 0.58, 0.595 becomes 0.60, 0.605 becomes
0.60, etc.
Alden
Paul Thornett wrote in message
<#lAmhvPY...@uppssnewspub04.moswest.msn.net>...