Wrox Programmer Forums Problem with Round Function
 |
 Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
 Welcome to the p2p.wrox.com Forums. You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com

May 16th, 2007, 09:10 PM
 darrenb Authorized User Join Date: Mar 2007 Posts: 53 Thanks: 0 Thanked 0 Times in 0 Posts
Problem with Round Function

Hello

I have a problem with the round function, if a value = 1.2345 the round figure is 1.23 however if a figure is 1.235 the round figure is 1.23 (should be 1.24 as 3rd figure is 5, i assume this would round up) is there a way i can set the round to round up if 3rd figure is =>5. I will post the expression i am using as well.

Round([Amount]*0.015,2)

Amount = a \$ Value ie (\$234.00) which i then get 1.5% of this figure and round to 2 decimal places.

Any Ideas

Regards

DB

May 17th, 2007, 10:49 AM
 allenm Friend of Wrox Join Date: Feb 2007 Posts: 163 Thanks: 0 Thanked 2 Times in 2 Posts

The round function works correctly for me with everything I've tried. What is the data type of [Amount]? Do you have the full code of the section where the rounding is going wrong? Perhaps it's being truncated before it's being rounded.

Try also to use:
Math.Round([Amount] * 0.015, 2)
Round(cDbl([Amount]) * 0.015, 2)

If all else fails you can do your own rounding:
--------------------------------------------------------------
Dim vTmp As Variant, iMakeInt As Long, fRightNumber As Double
vTmp = [Amount] * 0.015 'Check this value, make sure there are several decimal places, not truncated
vTmp = vTmp + .005 'Add the extra '5' to the appropriate decimal place
iMakeInt = Int(vTmp * 100) 'Check and see that the value is correct minus the decimal
fRightNumber = cDbl(iMakeInt / 100) 'fRightNumber should now contain right number
--------------------------------------------------------------
If any of the values above as you step through the equation returns a bad value then you may consider a detect and repair. You may want to anyway if you're using proper data types and Round isn't working right.

May 18th, 2007, 08:47 AM
 allenm Friend of Wrox Join Date: Feb 2007 Posts: 163 Thanks: 0 Thanked 2 Times in 2 Posts

Actually it seems like there was some problems with rounding depending on install and some inconsistencies even then. Found a really nice site on vb/vba rounding issues. It's quite extensive and you may find your answer there:
http://ewbi.blogs.com/develops/2003/...und_yet_a.html

May 22nd, 2007, 05:45 PM
 darrenb Authorized User Join Date: Mar 2007 Posts: 53 Thanks: 0 Thanked 0 Times in 0 Posts

Thanks for you suggestion

I went with the solution whick added .005 to the total ie [Amount]*0.015 then i add 0.005 which has resolved my problem.
After further investigation i found that the figures that were being supplied to me via excel had incorrect rounding figures as well. The 0.005 solution got us the closest to the figure required.

Thanks Again
Darren

 Similar Threads Thread Thread Starter Forum Replies Last Post Round() kingroon Classic ASP Basics 1 September 15th, 2008 01:17 PM Round Function in Derived column transformation loveyaseen BOOK: Professional SQL Server 2005 Integration Services ISBN: 0-7645-8435-9 0 October 31st, 2007 08:19 AM Decimal place problem...ROUND function! kuku SQL Server 2000 9 July 9th, 2005 05:50 PM round function help mohiddin52 Access 2 October 21st, 2004 07:14 AM Is there a round vbscript function that works? lcsgeek ASP.NET 1.0 and 1.1 Basics 8 August 20th, 2003 11:23 AM