round function not work property in access

[Guest]

Using the ROUND() function after performing mathematical calculations on
certain numeric values does not produce the correct result. The resulting
number is not rounded correctly to the proper decimal place.
ROUND(2068.1550000000,2)
The ROUND()function returns 2068.15. This is incorrect. The value is not
rounded correctly to the second decimal place. The correct value should be
2068.16.

How to settle in access?

 

[Guest]

Hi

I tried the following query...

select ROUND(2068.1550000000,2)

and tried vba code of...

msgbox ROUND(2068.1550000000,2)

and got 2068.16 in both cases.

Is there more to the calculation?

Andy Hull

 

[Allen Browne]

What version of Access are you seeing this in?

Open the Immediate Window (Ctrl+G), and enter:
? ROUND(2068.1550000000, 2)
Yields 2068.16 for me in A2003.

Chances are that whatever number you are using is subject to a floating
point inaccuracy:
http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems

For numbers that do not have floating point accuracy issues (such as the
fixed point type, Currency), you also need to be aware that Access uses
banker's rounding. When the final digit is a 5, if the previous digit is odd
the number is rounded up, and if even, it is rounded down. Example:
? Round(CCur(2.25), 1), Round(CCur(2.15), 1)

 

[Jamie Collins]

Using the ROUND() function after performing mathematical calculations on
certain numeric values does not produce the correct result. The resulting
number is not rounded correctly to the proper decimal place.
ROUND(2068.1550000000,2)
The ROUND()function returns 2068.15. This is incorrect. The value is not
rounded correctly to the second decimal place. The correct value should be
2068.16.

It's hard to determine your desired rounding algorithm based on a
sample of one value <g>. See:

http://support.microsoft.com/kb/196652

then search the archives for this group using the rounding algorithm's
name for a Jet SQL equivalent.

Jamie.

--

 

[Jamie Collins]

For numbers that do not have floating point accuracy issues (such as the
fixed point type, Currency), you also need to be aware that Access uses
banker's rounding. When the final digit is a 5, if the previous digit is odd
the number is rounded up, and if even, it is rounded down. Example:
? Round(CCur(2.25), 1), Round(CCur(2.15), 1)

"Access uses banker's rounding" is a bit of a sweeping statement <g>.
VBA's Round() expression indeed uses banker's rounding but this is not
a universal rounding algorithm for Access nor VBA e.g. the Format()
expression uses asymmetric rounding away from zero:

SELECT ROUND(CCUR(0.225), 2), ROUND(CCUR(0.215), 2),
FORMAT(0.225, 'Currency'), FORMAT(0.215, 'Currency')

Jamie.

--

 

[Allen Browne]

Jamie, you might like to try entering 0.225 and 0.215 into a JET field of
type Currency (to avoid the VBA-call-from-JET issues), and see how they
round.

 

[Jamie Collins]

Jamie, you might like to try entering 0.225 and 0.215 into a JET field of
type Currency (to avoid the VBA-call-from-JET issues), and see how they
round.

Those values won't round e.g. (ANSI-92 Query Mode):

CREATE TABLE Test
(
dec_col DECIMAL(19, 4),
cur_col CURRENCY
)
;
INSERT INTO Test (dec_col) VALUES (0.225)
;
INSERT INTO Test (dec_col) VALUES (0.215)
;
UPDATE Test SET cur_col = dec_col
;
SELECT cur_col
FROM Test
;

returns 0.2250 and 0.2150 respectively.

While we're on the subject, another example where Access/Jet does not
do banker's rounding:

DELETE FROM Test
;
INSERT INTO Test (dec_col, cur_col) VALUES (0.23456, 0.23456)
;
SELECT dec_col, cur_col
FROM Test
;

returns dec_col=0.2345 and cur_col=0.2346 i.e. the DECIMAL column did
not round because a column of type DECIMAL exhibits rounding by
symmetric truncation.

Jamie.

--

 

[Jamie Collins]

Those values won't round

Perhaps Access-not-Jet is the issue here: try this VBA + Jet (no
Access):

Sub DecColRound()
Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim sql As String
sql = _
"CREATE TABLE Test" & vbCr & "(" & vbCr & " dec_col" & _
" DECIMAL(19, 4)," & vbCr & " cur_col CURRENCY" & vbCr & ")"
.Execute sql
sql = _
"INSERT INTO Test (dec_col) VALUES" & _
" (0.225)"
.Execute sql
sql = _
"INSERT INTO Test (dec_col) VALUES" & _
" (0.215)"
.Execute sql
sql = _
"UPDATE Test SET cur_col = dec_col"
.Execute sql

sql = _
"SELECT cur_col" & vbCr & "FROM Test"

Dim rs
Set rs = .Execute(sql)
MsgBox rs.GetString

sql = _
"DELETE FROM Test"
.Execute sql
sql = _
"INSERT INTO Test (dec_col, cur_col)" & _
" VALUES (0.23456, 0.23456)"
.Execute sql

sql = _
"SELECT dec_col, cur_col" & vbCr & "FROM Test"

Set rs = .Execute(sql)
MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[Jamie Collins]

Jamie, you might like to try entering 0.225 and 0.215 into a JET field of
typeCurrency(to avoid the VBA-call-from-JET issues), and see how they
round.

Allen, the more I look at this the more confused I get <g>. Can you
clarify your point for me please? TIA

Jamie.

--

 

[Allen Browne]

I'm here to help people who are stuck Jamie.
You can find your own way, I'm sure.

 

[Jamie Collins]

I'm here to help people who are stuck

"Welcome to Microsoft Discussion Groups: Ask questions, share
information, or exchange ideas with others..."

I was merely asking you to clarify something you aimed directly at me.
No offence intended!

Jamie.

--