I need to always Round UP if greater than zero at all. HOW?

I am in office 2007 Access DB -
I have quantities that are greater than Zero but the Round function will
round down to 0, if the number is not over .5 - how do I get it to ALWAYS
round UP to the next nearest whole number even if it's .00001?

Try this --
IIF(Int([YourField])[YourField], Int([YourField])+1 , [YourField])

--
Build a little, test a little.


"RP" wrote:

I am in office 2007 Access DB -
I have quantities that are greater than Zero but the Round function will
round down to 0, if the number is not over .5 - how do I get it to ALWAYS
round UP to the next nearest whole number even if it's .00001?

If the number is always positive then the expression
-Int(-[YourField])
will always round up to the next integer value.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

KARL DEWEY wrote:
Try this --
IIF(Int([YourField])[YourField], Int([YourField])+1 , [YourField])

Friends and friends around the world.
We are presenting the new messenger.
He will be in the commissioning by the friends that we indicate to use the
system.
Access the system by clicking the link below and register free.

You get something for using orkut?
You get something for using skype?
You gain something by using twiter?
You get algumaocisa for using facebook?

Enjoy this is your time!!

Sign up and join for free.


http://www.sqipcom.com/?ref=webempreendedor

http://stakeholder.sqipcom.com/user/webempreendedor

Using an idea posted by James Fortune recently the following will round away
from zero to any interval:

Public Function RoundFromZero(dblVal As Double, dblTo As Double) As Double

Dim intUpDown As Integer

If dblVal 0 Then
intUpDown = 1
Else
intUpDown = -1
End If

RoundFromZero = intUpDown * (Int(dblVal / (intUpDown * dblTo))) * dblTo

End Function

So in your case to round to the nearest integer:

RoundFromZero([TheNumber], 1)

Conversely this rounds towards zero:

Public Function RoundToZero(dblVal As Double, dblTo As Double) As Double

Dim intUpDown As Integer

If dblVal 0 Then
intUpDown = -1
Else
intUpDown = 1
End If

RoundToZero = intUpDown * (Int(dblVal / (intUpDown * dblTo))) * dblTo

End Function

and this rounds up or down, i.e. 'up' rounds negative numbers towards zero,
positive numbers away from zero, and 'down' rounds negative numbers away from
zero, positive numbers towards zero. By default it rounds up; to round down
pass 1 in to the function as the optional third argument:

Public Function RoundTo(dblVal As Double, dblTo As Double, Optional intUpDown
As Integer = -1) As Double

' rounds up by default.
' to round down pass 1 into function as
' optional UpDown argument
RoundTo = intUpDown * (Int(dblVal / (intUpDown * dblTo))) * dblTo

End Function

Ken Sheridan
Stafford, England

RP wrote:
I am in office 2007 Access DB -
I have quantities that are greater than Zero but the Round function will
round down to 0, if the number is not over .5 - how do I get it to ALWAYS
round UP to the next nearest whole number even if it's .00001?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201003/1

"KenSheridan via AccessMonster.com" u51882@uwe wrote in
news:a4b9481ebbd89@uwe:

Using an idea posted by James Fortune recently the following will
round away from zero to any interval:

Public Function RoundFromZero(dblVal As Double, _
dblTo As Double) As Double
Dim intUpDown As Integer

If dblVal 0 Then
intUpDown = 1
Else
intUpDown = -1
End If

RoundFromZero = intUpDown * (Int(dblVal / (intUpDown * dblTo)))
* dblTo

End Function

I would recommend against doing all the calculations in one go, as
that's where floating-point errors creep in. I would change this:

RoundFromZero = intUpDown * (Int(dblVal / (intUpDown * dblTo))) *
dblTo

....to this:

dblDenominator = intUpDown * dblTo
dblTestValue = dblVal / dblDenominator
RoundFromZero = intUpDown * IntUpDown * dblTo

....that approach is based on the principles outlined by Luke Chung
in his old article on writing your own rounding function, where he
recommended storing the result of any calculation in a variable of
known precision so that you don't compound floating-point
inaccuracies.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

I take the point, David, and will implement it, but I think it will need to
be:

dblDenominator = intUpDown * dblTo
intTestValue = Int(dblVal / dblDenominator)
RoundFromZero = intUpDown * intTestValue * dblTo

Ken Sheridan
Stafford, England

David W. Fenton wrote:
Using an idea posted by James Fortune recently the following will
round away from zero to any interval:
[quoted text clipped - 13 lines]

End Function

I would recommend against doing all the calculations in one go, as
that's where floating-point errors creep in. I would change this:

RoundFromZero = intUpDown * (Int(dblVal / (intUpDown * dblTo))) *
dblTo

...to this:

dblDenominator = intUpDown * dblTo
dblTestValue = dblVal / dblDenominator
RoundFromZero = intUpDown * IntUpDown * dblTo

...that approach is based on the principles outlined by Luke Chung
in his old article on writing your own rounding function, where he
recommended storing the result of any calculation in a variable of
known precision so that you don't compound floating-point
inaccuracies.


--
Message posted via http://www.accessmonster.com

"KenSheridan via AccessMonster.com" u51882@uwe wrote in
news:a4c550d14717f@uwe:

I take the point, David, and will implement it, but I think it
will need to be:

dblDenominator = intUpDown * dblTo
intTestValue = Int(dblVal / dblDenominator)
RoundFromZero = intUpDown * intTestValue * dblTo

Hmm. I forgot about the Int() function, and so, there really needs
to be another variable:

dblDenominator = intUpDown * dblTo
dblTestValue = dblVal / dblDenominator
intTestValue = Int(dblTestValue)
RoundFromZero = intUpDown * intTestValue * dblTo

That's based on the principle of storing a calculation result in a
variable with a fixed accuracy, and then only performing
calculations on the variable.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Right, I'll do that. Appreciated as always.

Ken Sheridan
Stafford, England

David W. Fenton wrote:
I take the point, David, and will implement it, but I think it
will need to be:

dblDenominator = intUpDown * dblTo
intTestValue = Int(dblVal / dblDenominator)
RoundFromZero = intUpDown * intTestValue * dblTo

Hmm. I forgot about the Int() function, and so, there really needs
to be another variable:

dblDenominator = intUpDown * dblTo
dblTestValue = dblVal / dblDenominator
intTestValue = Int(dblTestValue)
RoundFromZero = intUpDown * intTestValue * dblTo

That's based on the principle of storing a calculation result in a
variable with a fixed accuracy, and then only performing
calculations on the variable.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201003/1

"KenSheridan via AccessMonster.com" u51882@uwe wrote in
news:a4d08e7debef2@uwe:

Appreciated as always.

As with so many things I post, they aren't original with me -- I'm
just passing on things I learned from people far smarter than I.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/