Does Access have Round Function?

[Anny]

I try to round my field as I did in Excel. Does Access
have this function.

 

[Van T. Dinh]

Round() function exists in A2K and AXP.

Check Access VB Help for syntax.

HTH
Van T. Dinh
MVP (Access)

 

[Allen Browne]

Arvi, I don't think the Round() function in Access 2000/2002 does banker's
rounding. The one by Ken Getz at:
http://www.mvps.org/access/modules/mdl0054.htm
provides that optionally.

The built-in one just works with whatever data type you pass in. That means
if you pass it a Double, it is subject to the rounding errors that always
dog floating-point numbers. If you need to avoid that, you can typecast the
variable into a Variant of subtype Decimal so you have a scaled number to
work with instead of a floating-poing one.

This function demonstrates the values that are returned differently if you
use scaled numbers. Change the constant to 3 or 4 to see the affected
numbers with more decimal places.

Function TestRound()
Dim lngI As Long
Dim lngFactor As Long
Dim dbl As Double 'Fractional value as Double.
Dim var As Variant 'Fractional value as Decimal.
Const conPlaces As Integer = 2

lngFactor = 10 ^ (conPlaces + 1)
For lngI = 1 To lngFactor
dbl = Round(lngI / lngFactor, conPlaces)
var = Round(CDec(lngI) / CDec(lngFactor), conPlaces)
If var <> dbl Then
Debug.Print dbl, var
End If
Next
End Function

 

[Arvi Laanemets]

Hi Allen

Test with A2000 (I don't have Office XP, so I'm not sure about it)

Table Test: ID, TestNumber(Double)
Continous form Test with fields TestNumber and RoundedNumber, where
RoundedNumber=Round([TestNumber];2)

Results:
TestNumber RoundedNumber
1.125 1.12
1.135 1.14


Arvi Laanemets

 

[Wayne Morgan]

XP is the same.

?Round(1.5, 0)
2
?Round(2.5, 0)
2

 

[Allen Browne]

So even (1.125) rounds down (1.12), and odd (1.135) rounds up (1.14)?

Yes, the Round() function basically does do that, but it's not consistent,
due to rounding errors. Try:
? Round(0.545,2), Round(0.575,2)
0.55 0.57

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hi Allen

Test with A2000 (I don't have Office XP, so I'm not sure about it)

Table Test: ID, TestNumber(Double)
Continous form Test with fields TestNumber and RoundedNumber, where
RoundedNumber=Round([TestNumber];2)

Results:
TestNumber RoundedNumber
1.125 1.12
1.135 1.14


Arvi Laanemets

Arvi, I don't think the Round() function in Access 2000/2002 does banker's
rounding. The one by Ken Getz at:
http://www.mvps.org/access/modules/mdl0054.htm
provides that optionally.

The built-in one just works with whatever data type you pass in. That means
if you pass it a Double, it is subject to the rounding errors that always
dog floating-point numbers. If you need to avoid that, you can typecast the
variable into a Variant of subtype Decimal so you have a scaled number to
work with instead of a floating-poing one.

This function demonstrates the values that are returned differently if you
use scaled numbers. Change the constant to 3 or 4 to see the affected
numbers with more decimal places.

Function TestRound()
Dim lngI As Long
Dim lngFactor As Long
Dim dbl As Double 'Fractional value as Double.
Dim var As Variant 'Fractional value as Decimal.
Const conPlaces As Integer = 2

lngFactor = 10 ^ (conPlaces + 1)
For lngI = 1 To lngFactor
dbl = Round(lngI / lngFactor, conPlaces)
var = Round(CDec(lngI) / CDec(lngFactor), conPlaces)
If var <> dbl Then
Debug.Print dbl, var
End If
Next
End Function