Round Function Question...... 1

[flaniganmj]

Howdy,

I don't know why I never noticed this but why does:

Round(1.566, 2)

not return the same value as

Round(1.565, 2)

Shouldn't they return the same value. What am I missing?

Thanks,

flan

 

[KornGeek]

My guess is that your 1.565 is actually 1.5649 or something like that.

 

[flaniganmj]

No, literally type in a sub:

*******************************************
Msgbox Round(1.565, 2)
*******************************************

It will return 1.56. It should return 1.57. At least I would have guessed it does. Though Access calcs it this way, in Excel it will calcuate it correctly (or at least what I deem as correct)........

So, I got around it with this:

*******************************************
Sub RoundTest()
'
Dim objExcel As New Excel.Application
'
With objExcel
MsgBox WorksheetFunction.Round(1.565, 2) ' Returns 1.57
End With
'
Set objExcel = Nothing
End Sub
********************************************

Proper rounding shouldn't be this complex and I have to wonder where else in other databases this may be an issue.

I would really like to hear an explanation as to why this happens.

Thanks for the input though,

flan

 

[KornGeek]

I tested this, and I agree, Access is doing this wrong.

Here are the values I tested and the results:
Round(1.564, 2) 1.56
Round(1.565, 2) 1.56
Round(1.566, 2) 1.57

I then got clever and did this test and got this result:
Round(Round(1.565, 3), 2) 1.56

That's weird. Have you had problems with other values as well, or is it just something quirky about 1.565?

 

[flaniganmj]

I store pricing data with 3 decimals. And every one that ends in 5, i.e. 1.655, doesn't round up, as it should. Six through 9 rounds up and 0 through 4 rounds down. This been one of those things, where you think back to how many reports went out incorrect. Very scary. I have researched this site, books, and development guides, and have had no luck finding a reason for or resolution to the problem.

Thanks for confirming I'm not crazy,

flan

 

[bubba100]

Rounding is a constant battle with Access. In Excel when you round, you round be it to 2 or 3 places. Access Figures to more than 10 places ( I don't remember the exact number) you have to force it to round with some "clever" computations. I have spent more time than I wish to addmit dealing with rounding.

 

[PaulBricker]

This will round any number you have to two places. 0 to 4 round down and 5 to 9 round up. I have some addtional FAQ's about rounding in the Access Queries and Jet SQL forum FAQ.

myVal:Int((myField + .005)*100)/100



Paul

 

[psemianonymous]

The term you're looking for is "Banker's rounding". If at .5, it will round to the nearest even number. Thus 1.565 will round to 1.560 instead of 1.570.

This (being Microsoft) uses bankers rounding. So technically this isn't a "bug", it's a "feature" no one is aware of.


--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[PaulBricker]

So what you are saying is that they save a penny any time they round .xx5 down. Actually that makes some sense now.
Thanks for the info.

Paul

 

[flaniganmj]

That's what I'm looking for!

Thanks,

flan