Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Rounding

Posted on 2006-11-14
31
Medium Priority
?
721 Views
Last Modified: 2012-05-05
I see Microsoft Access is not consistent in rounding. Interesting I know...I am trying to do banker's round ie. round(1.235)=1.24

If you want to see the description of banker's rounding:
http://support.microsoft.com/default.aspx?scid=kb;en-us;196652

which is round it to nearest even number...

I need two digits after period just like I have above.
Thanks

You can use this syntax to test in VBA:

Debug.Print Round(2.155, 2)
'I need this result to be 2.16
0
Comment
Question by:Midnight2005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 7
  • +3
31 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 17940091
It does round correctly

? round(2.155,2)
 2.16

? round(2.145,2)
 2.14

if you want it to alway round up

? round(2.145+.0001,2)
 2.15

ie.  add a very small amount less that the number of your significant decimals
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17940131
According to the Knowledge Base article you posted ....

In Visual Basic for Applications, the following numeric functions perform banker's rounding: CByte(), CInt(), CLng(), CCur(), and Round().


ET
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 1900 total points
ID: 17940152
Round is a VBA function (as opposed to belonging to Access).

It *should* follow Banker's rounding.  But I can imagine the unreliability of that from region to region.
You could make a function to force it...

Function fRound(varVal, intDP As Integer)
   
    If IsNull(varVal) Then Exit Function
   
    fRound = Round(Val(varVal * (10 ^ intDP))) / (10 ^ intDP)
   
End Function
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 1

Author Comment

by:Midnight2005
ID: 17940649
I don't know what you talking about, I don't find it if it's a funny joke.  Or maybe it's worse for Microsoft, it's totally random.

I went to About, it say Access 2003. (version 11) , Visual Basic version 6.3

I typed

Debug.Print Round(2.155, 2)
it's giving me 2.15...I don't know how you got 2.16.
Thanks

Note: I need Banker's Rounding, that's why I am not adding anything small.
Thanks for your help
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17940845
? round(2.15,1)
 2.2
? round(2.25,1)
 2.2
? round(2.35,1)
 2.4
? round(2.45,1)
 2.4

or

? round(2.435,2)
 2.44
? round(2.445,2)
 2.44
? round(2.455,2)
 2.46
? round(2.465,2)
 2.46
 
I'm runn A2K with SP3
0
 
LVL 1

Author Comment

by:Midnight2005
ID: 17940965
This is what I have:

? Round(2.155, 2)
 2.15
0
 
LVL 6

Expert Comment

by:gvlob
ID: 17941001
GRayL, what version of Access are you using. I tried ? round(2.155,2) and got the same thing as Midnight2005, 2.15. According to Banker's rounding, it should round to 2.16.

LPurvis, what did you mean by "...the ureliability of that from region to region."?
0
 
LVL 1

Author Comment

by:Midnight2005
ID: 17941021
From what he said I undertood GrayL is Usind Access 2000 (A2K)
0
 
LVL 6

Expert Comment

by:gvlob
ID: 17941025
GRayL, I just tried all your examples and got the same as you did, but for ? round(2.155,2) I get 2.15. What's the deal here?
0
 
LVL 6

Expert Comment

by:gvlob
ID: 17941107
It seems that it's looking at the tenths place for rounding. I just tried ? round(2.345) and got 2.35 not 2.34 like we would expect in Banker's Rounding.
? round(2.445) gives you 2.44 like we expect. So, this leads me to believe that Access 2k3 looks at the tenths place instead of the next greater significant value.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17941113
I get 2.15 too - but if Ray says he's getting 2.16 I believe him.
So it isn't consistent.  Hence my suggested function.

It behaves the same in A2K and A2K3 for me.

FWIW Bankers rounding does apply to higher DP - hence why the function is able to be so simple.
0
 
LVL 6

Expert Comment

by:gvlob
ID: 17941187
I agree with you LPurvis, I would go with a function. On the other hand, if GRayL is getting 2.16 there has to be a reason why.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17941210
There are many things I'd love to know about VBA and Access under the covers of which I've only able to deduce relatively small snippets over the years.
However MS has yet to call to enlighten me - so I write workarounds :-D
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17941230
Using 2.1n5 it rounded down on all n from 0 to 9.  When I changed either the 2 or .1 to some other digit and then varied n, it did the banker's round properly.  It looks like 2.1n5 is an anomaly.  Somebody should tell Bill;-)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17941244
I'll have a word when I'm next over for drinks.  :-)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17941254
To be truthful, I tested numbers other than 2.155 and just assumed 2.155 would behave normally.  For the record, 2.155 rounds down on my machine to 2.15.  
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17941295
I guess I have to Go To Jail - huh?
0
 
LVL 2

Expert Comment

by:metapher
ID: 17941389
I encountered this problem too in the past. And switched to the next version of Access. With the versions XP and following the Round-Issue wasn't a problem any more. My old comment:

The original Function Round() in VBA is not working correct, because it rounds always towards the next integer dividable by 2!!! There 1.5 and 2.5 will be rounded to 2!!! What a mess.

So it is not random where it rounds to. Always the even numbers are preffered. If you are not able to switch to a never Version of ACCESS you can help yourself in very creative ways: i.E. to a string and the back. I never tried, but changed my version..

Good luck!
0
 
LVL 1

Author Comment

by:Midnight2005
ID: 17941462
I am using the latest version 2003 :)
0
 
LVL 6

Expert Comment

by:gvlob
ID: 17941862
GRayL, you go Directly to Jail, do not pass Go, do not collect $200 (in this case 500 points) very big :-)
0
 
LVL 1

Author Comment

by:Midnight2005
ID: 17941984
What do I do guys?
Have I been the only guy in planet who needed a simple Banker's rounding?
0
 
LVL 6

Assisted Solution

by:gvlob
gvlob earned 100 total points
ID: 17942040
You could use your own custom function like LPurvis suggested, but it seems to be consistent on the tenths place. This should give you the same average as it would if it would work in the next significant figure.
0
 
LVL 1

Author Comment

by:Midnight2005
ID: 17942118
You're right, I am sure I can figure out something. It was fun. Thanks guys
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17942135
If you save the function to a standard module - then you can use fRound *everywhere* that you would have used Round.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17949286
Leigh, can you 'explain' what your function does - especially the contribution of Val()?
0
 
LVL 6

Expert Comment

by:gvlob
ID: 17949455
I don't think you need Val() in the function.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17949462
Hi Ray - good to see they've let you out on Day Release! :-)
Seemed harsh to me in the first place.

For any interested parties...

First off Val is to make sure there's no ambiguity about data types.  We're dealing with a doube (or single - semantics ;-)
VBA can make guesses and we don't want that.
If you try it without that there you'll still get the inconsistent result.

Ultimately the core of the function raises the number of the index of 10 required to get to 1 dp (based on the number of dp's we want to end up with).
Then let's VBA perform a standard round on no dp's and divides the result by that power of 10 again to get back to where we were (and with the correct no of DP's).

That's all a pretty standard rounding technique.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17949635
gvlob: I tested and it is necessary.  

Leigh: Thanks, as usual, an elegant solution.
0
 
LVL 1

Author Comment

by:Midnight2005
ID: 17949642
That's why I chosed the Excelent in rating. Thanks...
0
 
LVL 6

Expert Comment

by:gvlob
ID: 17949647
You learn something everyday :-)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17950034
Cheers Ray.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question