I want to use the ROUNDUP function from Excel in Access

[Guest]

The Excel Roundup Function is very handy and I would like to use it in
Access, but so far have only discovered the Round function. There are
certain times, I want my computations to round up to the next whole number
despite what traditional rounding produces. Can I make this happen in Access?

Any help is greatly appreciated.

 

[strive4peace]

Hi Lele,

how about this:

Int(expression + 0.5)

if you have negative numbers and you want Int to work right (ie: -7.5
--> -7 instead of -8), use

Fix(expression + 0.5)

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[JK]

This should do it.

If the number (Expr) is *always* potistive:

=Int(Expr)+IIf(Expr-Int(Expr)>0,1,0)

To allow for negative numbers

=(Int(Abs(Expr))+IIf(Abs(Expr)-Int(Abs(Expr))>0,1,0))*IIf(Expr>=0,1,-1)

Regards/JK

 

[James A. Fortune]

The Excel Roundup Function is very handy and I would like to use it in
Access, but so far have only discovered the Round function. There are
certain times, I want my computations to round up to the next whole number
despite what traditional rounding produces. Can I make this happen in Access?

Any help is greatly appreciated.

This will probably be more help than you want.

Take a look at:

http://groups.google.com/group/microsoft.public.access/msg/9614be58d0485dd0

That's enough if you don't need the decimal case. I just finished
looking at the O97 Excel help file entry for ROUNDUP. I'll repeat it
here for reference:

'-----------------Begin Quote-----------------
ROUNDUP

Rounds a number up, away from 0 (zero).

Syntax

ROUNDUP(number,num_digits)

Number is any real number that you want rounded up.
Num_digits is the number of digits to which you want to round number.

Remarks

· ROUNDUP behaves like ROUND, except that it always rounds a number up.
· If num_digits is greater than 0 (zero), then number is rounded up to
the specified number of decimal places.
· If num_digits is 0 or omitted, then number is rounded up to the
nearest integer.
· If num_digits is less than 0, then number is rounded up to the left of
the decimal point.

Examples

ROUNDUP(3.2,0) equals 4

ROUNDUP(76.9,0) equals 77

ROUNDUP(3.14159, 3) equals 3.142

ROUNDUP(-3.14159, 1) equals -3.2

ROUNDUP(31415.92654, -2) equals 31,500
'-----------------End Quote-----------------

The full functionality might be something like:

ROUNDUP(X, I) = - Int( - X * 10 ^ I) / 10 ^ I

But ROUNDUP(-3.14159, 1) gives -3.1 instead of -3.2 so try:

Public Function RoundUp(X As Double, I As Integer) As Double
RoundUp = -Sgn(X) * Int(-Sgn(X) * X * 10 ^ I) / 10 ^ I
End Function

The Sgn(X) essentially treats X as positive for the calculation, then
adjusts the sign.

'or omitted':

Public Function RoundUp(X As Double, Optional I As Integer) As Double
If IsMissing(I) Then
RoundUp = -Int(-X)
Else
RoundUp = -Sgn(X) * Int(-Sgn(X) * X * 10 ^ I) / 10 ^ I
End If
End Function

You should test this more than I have before using this Excel function
clone. Making Crystal's idea work for the general case is a little more
difficult. I'll give it a shot though.

James A. Fortune
(e-mail address removed)

 

[Guest]

Hi Lele,

To add some to the other answers you have received, I believe you could call
the Excel Roundup function directly from Access. Here is a sample KB article
written for Access 2000, but the same idea should work with later versions of
Access and Excel. In step 3, you would simply set a reference for the
appropriate version of Excel (9 for Excel 2000, 10 for Excel 2002, 11 for
Excel 2003, etc.). If you are really industrious, you could likely even
convert these early bound code samples to late bound code, which does not
require a checked library reference for the Excel object library.

Disclaimer: I did not test this beforehand, calling the Roundup function,
but it should work. I have used this method to call other Excel functions in
the past. The only ones that seem to be very difficult to call are those that
require an array of values passed in as input.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[James A. Fortune]

Hi Lele,

how about this:

Int(expression + 0.5)

if you have negative numbers and you want Int to work right (ie: -7.5
--> -7 instead of -8), use

Fix(expression + 0.5)

Warm Regards,
Crystal

Using your idea I came up with:

Public Function RoundUp(X As Double, Optional I As Integer) As Double
If IsMissing(I) Then
If X >= 0 Then
RoundUp = Round(X + 0.49999999, 0)
Else
RoundUp = Fix(X)
End If
Else
If X >= 0 Then
RoundUp = Round(X * 10 ^ I + 0.49999999, 0) / 10 ^ I
Else
RoundUp = Round(X * 10 ^ I - 0.49999999, 0) / 10 ^ I
End If
End If
End Function

For negative numbers Fix(X) = Sgn(X) * Int(Abs(X)) = - Int(-X). That
worked out nicely. Using 0.5 didn't work when X = 0.

-Sgn(X) * X can be replaced by -Abs(X) in the other function I posted.
I'm still looking for a way to use the Fix function in the final case
for the function shown above. It's also interesting to note that Sgn(Y
* Z) = Sgn(Y) * Sgn(Z).

James A. Fortune
(e-mail address removed)

 

[Guest]

Hi Thanks for your suggestion. Unfortunately, I am not sure how to use it.

Do I save the code in a module?

I tried doing just that and then putting ROUNDUP infront of my expression in
my query which is the basis of my form but it did not recognize the ROUNDUP
FUNCTION and produced an error.

 

[James A. Fortune]

Hi Thanks for your suggestion. Unfortunately, I am not sure how to use it.

Do I save the code in a module?

I tried doing just that and then putting ROUNDUP infront of my expression in
my query which is the basis of my form but it did not recognize the ROUNDUP
FUNCTION and produced an error.

To make the function more query friendly, try changing:

Public Function RoundUp(X As Double, Optional I As Integer) As Double
If IsMissing(I) Then
RoundUp = -Int(-X)
Else
RoundUp = -Sgn(X) * Int(-Sgn(X) * X * 10 ^ I) / 10 ^ I
End If
End Function

To:

Public Function RoundUp(X As Variant, Optional I As Integer) As Variant
RoundUp = X
If IsNull(X) Then Exit Function
If IsMissing(I) Then
RoundUp = -CDbl(Int(-X))
Else
RoundUp = -CDbl(Sgn(X) * Int(-Sgn(X) * X * 10 ^ I) / 10 ^ I)
End If
End Function

Then you can call it in a query like (paste into SQL View with
appropriate names):

SELECT SomeField, RoundUp(MyFieldToRoundUp, 2) As RoundedUpValue FROM
MyTable;

or

SELECT SomeField, RoundUp(MyFieldToRoundUp) As RoundedUpValue FROM MyTable;

You can also use Query By Example (Query Design Mode) by placing
RoundedUpValue: RoundUp(MyFieldToRoundUp) in a Field box.

Using Variants will make the function behave nicely if the table
contains Null values. You could also try changing RoundUp wherever it
occurs to RndUp to check for a name conflict. I don't think that
RoundUp is a reserved word, but a reference to Excel could confuse
Access given that a ROUNDUP function exists there. Change to use
Variants in a similar fashion if you want to try the other version.

James A. Fortune
(e-mail address removed)

 

[strive4peace]

Hi Lele,

Just try this on the grid:

field --> WhateverYouWantToCallIt: Fix([fieldname] + 0.5)

Fix is like Int except that it corrects for negative numbers...

Int truncates the expression

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*