?
Solved

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

Posted on 2012-03-09
8
Medium Priority
?
273 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
[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 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 60

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

650 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