?
Solved

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

Posted on 2012-03-09
8
Medium Priority
?
275 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!
0
Comment
Question by:SeyerIT
8 Comments
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 800 total points
ID: 37702851
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
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 37703074
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
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 800 total points
ID: 37703215
Yes, use the Round15 from this link which also performs a perfect 4/5 rounding:

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

/gustav
0
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.

 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 400 total points
ID: 37708381
@gustav

Why not use the Round17 code?
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 37708563
Right, sorry, I didn't notice but did a copy and past from an old post.

/gustav
0
 
LVL 46

Expert Comment

by:aikimark
ID: 37709235
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.
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37709321
Thanks all for your help! Sorry I'm just now getting back to this. Those seem to work better.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 37709865
You are welcome!

And yes, that Gustav is me.

/gustav
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

569 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