Jennifer Gross
jengross at gte.net
Fri Jun 6 15:47:57 CDT 2008
Hi Shamil,
You're right on all counts. I have been using this function for so many
years it may very well have come from that Smart Access article.
Interesting about the negative numbers. Since I don't typically work with
them I haven't had to think about them in that way - what is up v. down. I
may just have to change my much used function, but I need it to let me
determine the number of decimal places, so I can't use Int.
Jennifer
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Friday, June 06, 2008 11:34 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Rounding UP
<<<
The RoundUp function I posted also works with negative numbers...
>>>
Hi Jennifer,
Yes, I know - I'm MS Access veteran, you know :) - here is where your
function comes from I guess:
<<<
When Microsoft Access Math Doesn't Add Up
http://www.fmsinc.com/TPapers/math/index.html
Originally published in Smart Access October 1997
>>>
There were lengthy debates on Rounding in MS Access 10+ years ago - and here
is why this referred above article was published by Luke Chung...
I'd note also that this thread's main subject is to *always* round *up* to
*Integers*:
- Your function will round *down* 1.2 to 1 and will round *up* -1.2 to -1
- My function will round *up* 1.2 to 2 and will round *down* -1.2 to -2
I suppose, the latter feature was requested by the originator of this
thread, not the former - please feel free to correct me if I'm wrong.
I can't remember I have read anywhere about this "trick":
Public Function RoundUp(ByVal value As Double)
RoundUp = Sgn(value) * Abs(Int(-Abs(value)))
End Function
I could have missed - it *should* have been published somewhere already...
Thank you.
--
Shamil
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jennifer Gross
Sent: Friday, June 06, 2008 11:09 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Rounding UP
The RoundUp function I posted also works with negative numbers, and it lets
you select the number of digits past the decimal place that you want to
round to - particularly handy when you are doing currency calculations and
need to round to 2 digits past the decimal place. I have other instances,
like with time tracking where I want to round to 1 digit past the decimal
place. So one all encompassing function that is always in every database I
create works really well. In fact I get so used to using it that when I
inherit a database the first time I go to use the function and it throws an
error it takes me a moment to remember that it is not a built-in function
and I have to add it.
Jennifer
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Friday, June 06, 2008 9:35 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Rounding UP
Even better?:
Public Function RoundUp(ByVal value As Double)
RoundUp = Sgn(value) * Abs(Int(-Abs(value))) End Function
It will work for negative values also....
--
Shamil
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stephen
Sent: Friday, June 06, 2008 8:28 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Rounding UP
Precisely ! And far more elegant ..... :-)
Stephen Bond
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Saturday, 7 June 2008 4:22 a.m.
To: Stephen
Subject: Re: [AccessD] Rounding UP
abs(int(-(YourFpNumber))) ?
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stephen
Sent: Friday, June 06, 2008 7:59 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Rounding UP
I suspect a simple ROUND as specified will have an undesirable side-effect.
It works in most cases but not all. If, for example, you add 12 and a half
percent to 8, you get 9 - exactly. This formula
round(YourFpNumber + 0.5,0)
will take the 9 and make it 10. IOW, adding 12 and a half percent to 8 will
give 9 exactly, then the formula above takes it to 10.
Is this what you want to happen?
If it isn't then you need something a bit more complex. Using 1.125 for an
increase of 12 and a half percent, in a query
new:
IIf(Int(1.125*[old])=1.125*[old],1.125*[old],Round(0.5+1.125*[old],0))
should take care of all cases.
BTW, Excel has a wonderful ROUNDUP function which would do this in a snip.
Not in Access 2003.
Stephen Bond
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jeff Barrows
Sent: Saturday, 7 June 2008 3:31 a.m.
To: Stephen
Subject: Re: [AccessD] Rounding UP
Thanks! That did the trick!
On 6/6/08, Heenan, Lambert <Lambert.Heenan at aig.com> wrote:
>
> Try: round(YourFpNumber + 0.5,0)
>
> Lambert
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jeff
> Barrows
> Sent: Friday, June 06, 2008 11:10 AM
> To: accessd
> Subject: [AccessD] Rounding UP
>
> In Access 2003, is there a way to ALWAYS round UP to the next whole
number?
> I am trying to calculate stock needed to create parts and need to
> determine whole pieces of stock, not partials.
>
> TIA
>
> Jeff Barrows
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com