Solved

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

Posted on 2012-03-09
8
269 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 200 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 51

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 49

Accepted Solution

by:
Gustav Brock earned 200 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 45

Assisted Solution

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

Why not use the Round17 code?
0
 
LVL 49

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 45

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 49

Expert Comment

by:Gustav Brock
ID: 37709865
You are welcome!

And yes, that Gustav is me.

/gustav
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

821 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