CrystalSurfer
Matrix activist
- Local time
- Today, 14:01
- Joined
- Jan 11, 2006
- Messages
- 75
Hi All,
I use the following generally available function for rounding.
I thought it cured the problem with 'Bankers' rounding which is Access' default method of rounding.
But can someone tell me why, when using this function on the value 4.725 it returns 4.72 when it should return 4.73?
I am using it in this instance within a query to round off (to the nearest) a sales tax (vat) calc. ie:
vbaRound(Abs(qryInvoiceExport02.NetAmount)*0.175,2)
As Im not specifying a parameter to round up or down the function will by default round off.
I've broken down the single line of code into constituent parts to see whats happening and what happens is the "Int(dblValue)" on the 3rd line contains what I expect (473) but after execution vbaRound contains 4.72!!
Am I missing something obvious here?
Thanks.
I use the following generally available function for rounding.
I thought it cured the problem with 'Bankers' rounding which is Access' default method of rounding.
But can someone tell me why, when using this function on the value 4.725 it returns 4.72 when it should return 4.73?
I am using it in this instance within a query to round off (to the nearest) a sales tax (vat) calc. ie:
vbaRound(Abs(qryInvoiceExport02.NetAmount)*0.175,2)
As Im not specifying a parameter to round up or down the function will by default round off.
I've broken down the single line of code into constituent parts to see whats happening and what happens is the "Int(dblValue)" on the 3rd line contains what I expect (473) but after execution vbaRound contains 4.72!!
Am I missing something obvious here?
Thanks.
Code:
Public Function vbaRound(dblValue As Double, intDecimals As Integer, _
Optional RoundOpt As enumRoundOpt = rNearest) As Double
'Public Function vbaRound(dblValue As Double, intDecimals As Integer,
'*******************************************************************
' PURPOSE: To replace VBA's Round() function
'
' AUTHOR:
'
' ARGUMENTS:
' 1) dblValue - Value to Round
' 2) intDecimals - Decimal places to round to (any positive integer)
' 3) enumRoundOpt - To specify whether to round to nearest, up or down.
' a) Valid argument values are
' i) rNearest (equivalent to 0)
' ii) rUp (equivalent to 1)
' iii) rDown (equivalent to 2)
' iv) Any other number results in rounding to the nearest
'
' RETURNS:
' Result is a double value.
' e.g.
' vbaRound(12.3456, 2, rNearest) = 12.35
' vbaRound(12.3456, 2, rUp) = 12.35
' vbaRound(12.3456, 2, rDown) = 12.34
'
' - http://AccessDB.Info
On Error GoTo HandleErr:
Dim dblPlacesFactor As Double
Dim dlbRoundFactor As Double
Dim dblCalc As Double
dblPlacesFactor = 10 ^ intDecimals
Select Case RoundOpt
Case rNearest 'Round to Nearest
dlbRoundFactor = 0.5
Case rUp 'Round UP
dlbRoundFactor = 1
Case rDown 'Round DOWN
dlbRoundFactor = 0
Case Else 'Round to Nearest by default for all other values
dlbRoundFactor = 0.5
End Select
'Calculate rounded value & Return value
'Original code:
'vbaRound = Int((dblValue * dblPlacesFactor) + dlbRoundFactor) / dblPlacesFactor
'Code broken down into component parts:
dblValue = dblValue * dblPlacesFactor
dblValue = dblValue + dlbRoundFactor
vbaRound = Int(dblValue) / dblPlacesFactor
ExitHere:
Exit Function
HandleErr:
'Leave value by default alone if an error occurs
'OR set it to any value you prefer (e.g. 0)
vbaRound = dblValue
Resume ExitHere:
End Function
