We help IT Professionals succeed at work.

Looking for a faster solution to a rounding function in access query.

Jarred Meyer
Jarred Meyer asked
on
Medium Priority
292 Views
Last Modified: 2012-08-13
I am looking to see if there is a faster alternative to the Rounding function we picked up from a previous question posted. Currently using a function called Round16. It looks like;

Public Function Round16(ByVal v As Double, Optional ByVal lngDecimals As Long = 0) As Double
' by Filipe Lage, fclage-NO~SPAM@kiss-ezlink.com, 20050322
  Round16 = CDbl(Format$(v * 10 ^ lngDecimals, "0")) / 10 ^ lngDecimals
End Function


This is working properly but it is slow. Anyone know of a faster alternative to this? The built in Round() function is not rounding properly for some reason.

Thanks for the help!
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2009
Commented:
Rounding in Access is more complicated than you might think.  See Access Archon #94 (a guest article by Geoff Shepherd) for lots of info on rounding.

http://www.helenfeddema.com/Files/accarch94.zip
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
dont use function, use it in sql... does it make any difference?

select Round16(v, 2) as rounded_value from mytable
>>>
select CDbl(Format$(v * 10 ^ 2, "0")) / 10 ^ 2 as rounded_value from mytable
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Yes, use the Round15 from this link which also performs a perfect 4/5 rounding:

http://www.xbeat.net/vbspeed/c_Round.htm

/gustav
CERTIFIED EXPERT
Top Expert 2014
Commented:
@gustav

Why not use the Round17 code?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Right, sorry, I didn't notice but did a copy and past from an old post.

/gustav
CERTIFIED EXPERT
Top Expert 2014

Commented:
btw...are you the gustav that contributed Round12 & Round15?  If so, thank you for your participation in that wonderful little repository of VB goodness/speediness.
Jarred MeyerProduction Manager

Author

Commented:
Thanks all for your help! Sorry I'm just now getting back to this. Those seem to work better.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

And yes, that Gustav is me.

/gustav

Explore More ContentExplore courses, solutions, and other research materials related to this topic.