General discussion
-
CreatorTopic
-
September 10, 2003 at 1:33 am #2316276
Rounding Down In Access Take 2
Lockedby d_bones · about 18 years, 8 months ago
Does anyone know how to round down numbers in Access. i.e. 99.999 becomes 99.99 not 100.00.
Any function or formula will do. I need the same as the Excel function Rounddown.
Thanks to who previously suggested convert to an integer, but I think you missed the point.
Any help gratefully received
Topic is locked -
CreatorTopic
All Comments
-
AuthorReplies
-
-
September 10, 2003 at 4:36 am #3380453
Reply To: Rounding Down In Access Take 2
by sachin · about 18 years, 8 months ago
In reply to Rounding Down In Access Take 2
Syntax: Round(expression[, numdecimalplaces])
The Access Round() function appears to be a handy way of rounding decimal values to a given number of decimal places. For example, Round(2.341, 2) returns 2.34.
However, it does not perform rounding the way you might expect. Take this example:
Round(2.385,2)
You would expect this to return 2.39, but if you open an immediate window and type ?Round(2.385,2) you will get the following output:
2.38 (not 2.39)
Even though the last digit was 5, it rounded DOWN.
If this is not the behaviour you expected, then here?s the explanation:
The Round() function does a round-to-even, not the more well-known round-to-larger. If the argument ends in 5, the number may be rounded up or down to achieve an even rightmost figure.
So in our example above:
2.385 could be rounded to either 2.38 or 2.39. The former has an even rightmost figure, so this is the result.
The reasoning behind this is to eliminate cumulative errors over a series of calculations. Put another way, 100 half pennies should round to be equal 50 cents, not 100 cents.
A RoundToLarger() function:
Public Function RoundToLarger(dblInput As Double, intDecimals As Integer) As Double
‘Implements a variant of the Round() function, that rounds-to-larger
‘rather than rounds-to-even:Dim strFormatString As String ‘Format string
‘If input is zero, just return zero. Else format as appropriate:
If dblInput <> 0 Then
strFormatString = “#.” & String(intDecimals, “#”)
RoundToLarger = Format(dblInput, strFormatString)
Else
RoundToLarger = 0
End IfEnd Function
-
September 11, 2003 at 7:52 am #3380049
Reply To: Rounding Down In Access Take 2
by d_bones · about 18 years, 8 months ago
In reply to Reply To: Rounding Down In Access Take 2
Many thanks but this still rounds 99.998 to 100 We need to round down in every instance regardless of the third or subsequent decimal place.
-
September 11, 2003 at 7:54 am #3380048
Reply To: Rounding Down In Access Take 2
by d_bones · about 18 years, 8 months ago
In reply to Reply To: Rounding Down In Access Take 2
Many thanks but this still rounds 99.998 to 100 We need to round down in every instance regardless of the third or subsequent decimal place.
-
-
September 10, 2003 at 4:42 am #3380451
Reply To: Rounding Down In Access Take 2
by sachin · about 18 years, 8 months ago
In reply to Rounding Down In Access Take 2
SELECT CLng( number + .5) as Expr;
This statement works in Access 2000 and will round up any number to the next whole number. For example:
2.00001 -> 3
2.9 -> 3
2.0 -> 2-
September 11, 2003 at 7:54 am #3380047
Reply To: Rounding Down In Access Take 2
by d_bones · about 18 years, 8 months ago
In reply to Reply To: Rounding Down In Access Take 2
Sorry but we want to round down to two decimal places not up to an integer- thanks for the reply
-
-
September 12, 2003 at 1:18 am #2743384
Reply To: Rounding Down In Access Take 2
by d_bones · about 18 years, 8 months ago
In reply to Rounding Down In Access Take 2
Many Thanks for the suggestions. Finaly found the answer on Microsoft Knowledge Base Article 210564 – Round or Truncate Currency Values 🙂
-
September 16, 2003 at 8:09 am #2741761
Reply To: Rounding Down In Access Take 2
by pancras · about 18 years, 8 months ago
In reply to Rounding Down In Access Take 2
int() cuts off the decimal part.
so int(x) leaves you with the whole part.
int(99.999*100)/100 gives 99.99 should do the trick-
September 29, 2003 at 2:21 pm #3389467
Reply To: Rounding Down In Access Take 2
by pancras · about 18 years, 8 months ago
In reply to Reply To: Rounding Down In Access Take 2
i think the points are mine. cause that is what exactly there in the kb
-
-
September 17, 2003 at 6:19 pm #2744635
Reply To: Rounding Down In Access Take 2
by techrep · about 18 years, 8 months ago
In reply to Rounding Down In Access Take 2
This is not very elegant but it works. Convert the number to a string and truncate like so:
Function RoundDown(TheNum As Double, Decimals As Integer) As Double
Dim buf As String, pos As Integer, Rounded As Stringbuf = CStr(TheNum) ‘Convert the number to a string
pos = InStr(buf, “.”) ‘locate decimal point
If pos = 0 Then ‘if no decimals
Rounded = TheNum
Else
Rounded = Left(buf, pos + Decimals)
End If
RoundDown = Val(Rounded) ‘convert to numericEnd Function
-
-
AuthorReplies