[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