How to do ceiling in Access

[Boon]

What I want to do is round the number up to the next 2 digit decimal value.

for instance.

34.567 --> 34.57
34.561 --> 34.57
34.56 --> 34.56


I use the following code but it gave me a wrong result.

Dim value As Double

value = 34.56
Debug.Print -Int(-value * 100) / 100


the code print out the value of 34.57!! I would defenitely expect to see
the result is 34.56.

Help please....

thanks,
Boon

 

[Stefan Hoffmann]

hi Boon,

What I want to do is round the number up to the next 2 digit decimal value.

for instance.

34.567 --> 34.57
34.561 --> 34.57
34.56 --> 34.56

Try this:

Int((value + 0.004) * 100) / 100


mfG
--> stefan <--

 

[Tom van Stiphout]

Oh, Stephan, was that a slip of the pen? Trying is not part of the
game.
Run this:
Sub test1()
Dim value As Double
Dim i As Integer
value = 34.55
For i = 0 To 10
Debug.Print i, value, Int((value + 0.004) * 100) / 100
value = value + 0.001
Next i
End Sub
results:
0 34.55 34.55
1 34.551 34.55
2 34.552 34.55
3 34.553 34.55
4 34.554 34.55
5 34.555 34.55
6 34.556 34.55
7 34.557 34.56
8 34.558 34.56
9 34.559 34.56
10 34.56 34.56
So this formula is correct in some cases, but incorrect in the general
case.

-Tom.
Microsoft Access MVP

 

[Marshall Barton]

What I want to do is round the number up to the next 2 digit decimal value. []
I use the following code but it gave me a wrong result.

Dim value As Double
value = 34.56
Debug.Print -Int(-value * 100) / 100

the code print out the value of 34.57!! I would defenitely expect to see
the result is 34.56.

That's because floating point numbers are only computer
binary approximations of real numbers. This is the same
problem you have trying to write the decimal value of 1/3 on
a piece of paper.

Try this:
?34.56# - 34
and you will see the problem with trying to do exact
calculations with floating point.

If your values are limited to 4 decimal places, you can use
the Currency type, which are fixed point numbers.

If you can not do that, then you will have to decide how
much precision you require and either create your own fixed
point calculation scheme or use some kind of fudge factor to
allow for small discrepancies in the approximation.

 

[Arvin Meyer [MVP]]

The easiest way is just to use the XL function:

Public Function XLCeiling(dblNum As Double, dblUp As Double) As Double
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
XLCeiling = objXL.WorksheetFunction.Ceiling(dblNum, dblUp)
End Function

You can also create a function to round up (or down) in Access.

 

[David W. Fenton]

The easiest way is just to use the XL function:

Public Function XLCeiling(dblNum As Double, dblUp As Double) As
Double Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
XLCeiling = objXL.WorksheetFunction.Ceiling(dblNum, dblUp)
End Function

May I suggest that if you're going to do this, don't recreate the
object each time you call the function, but use a static variable,
instead:

Public Function XLCeiling(dblNum As Double, dblUp As Double) _
As Double
Static objXL As Object

If objXL Is Nothing Then
Set objXL = CreateObject("Excel.Application")
End If
XLCeiling = objXL.WorksheetFunction.Ceiling(dblNum, dblUp)
End Function

Until a few years ago, before I comprehended the beauty of static
variables, I used to use a module-level variable for this, but it's
much neater to have the variable definition inside the function
where you're using it.

 

[Tom van Stiphout]

On 18 Mar 2010 22:53:45 GMT, "David W. Fenton"

Absolutely true. Nice work, David.

One optimization I would like to see is to not load the entire
Excel.Application object (which as we know loads the entire excel.exe
in memory), but perhaps chirurgically tap into one of Excel's DLLs to
ferret out this function without the massive (but fortunately
one-time) overhead.

-Tom.
Microsoft Access MVP

 

[Stefan Hoffmann]

hi Tom,

Oh, Stephan, was that a slip of the pen? Trying is not part of the
game.

I'm sometimes believe I already mastered the Tao

Ah, but you're right.

Fix(value * 100 + 1) / 100


mfG
--> stefan <--

 

[David W. Fenton]

One optimization I would like to see is to not load the entire
Excel.Application object (which as we know loads the entire
excel.exe in memory), but perhaps chirurgically tap into one of
Excel's DLLs to ferret out this function without the massive (but
fortunately one-time) overhead.

Wow, that's way more than I'd be willing to do!

Also, the code that I supplied really ought to have an optional
argument that forces the loaded application closed:

Public Function XLCeiling(dblNum As Double, dblUp As Double, _
Optional bolClose As Boolean = False) As Double
Static objXL As Object

If bolClose And Not (objXL Is Nothing) Then
objXL.Quit ' you might not want to do this
Set objXL = Nothing
Else
If objXL Is Nothing Then
Set objXL = CreateObject("Excel.Application")
End If
XLCeiling = objXL.WorksheetFunction.Ceiling(dblNum, dblUp)
End If
End Function

I've not supplied a return value for the path when you're closing
the existing object because there's no meaningful value that could
be returned. You would never be looking for a return value in that
case, anyway, seems to me.

In fact, I'd tend to write this in two parts, with the Excel object
being independent of the call to an Excel function:

Public Function objXL(Optional bolClose As Boolean) As Object
Static obj As Object

If bolClose And Not (obj Is Nothing) Then
obj.Quit ' you might not want to do this
Set obj = Nothing
Else
If obj Is Nothing Then
Set obj = CreateObject("Excel.Application")
End If
Set objXL = obj
End If
End Function

Public Function XLCeiling(dblNum As Double, dblUp As Double, _
As Double
XLCeiling = objXL.WorksheetFunction.Ceiling(dblNum, dblUp)
End Function

The first time you call XLCeiling, it will silently initialize
objXL. When your app closes, you'd call objXL(True) to release the
memory.

I do this with the File System Object in many of my applications.
It's also the principle behind my dbLocal() function that replaces
CurrentDB().

 

[Arvin Meyer [MVP]]

IIRC, Static variables have a scope equal to the life of the function,
unless they are declared at form or application wide level like a global
variable. The reason for using them is so the variable won't reinitialize.
Since the variable is called only once, and never reinitialized, there is no
advantage, in this particular case, to using a static variable. At the
global or even form level, unless this function is used repeatedly
throughout the session, Excel.exe stays in memory the entire time. That's
only efficient if the function is to be used in a query were it may be
called multiple times. Also, I was typing aircode and didn't bother to clean
up or use error handling. That too would make the function better.

 

[Arvin Meyer [MVP]]

The more I think about it. depending upon how the Ceiling () function is
used, Excel doesn't even necessarily need to come into the picture. In
Excel, the Ceiling() function is generally used to round up by a specified
amount. More than 10 years ago, I wrote a rounding function (there wasn't
one in Access at the time) which did just that:

Public Function CurRnd(amt As Variant, Optional nearest As Variant) As
Currency
'--------------------------------------------------------------------
' Name: CurRnd
' Purpose: Currency rounding function that handles division by zero
' Inputs: amt As Variant
' nearest As Variant
' Returns: Currency
' Author: Arvin Meyer
' Date: March 25, 1999
' Comment: Error 11 is division by zero
'--------------------------------------------------------------------
On Error GoTo Err_CurRnd

If IsNumeric(amt) Then
If IsMissing(nearest) Then nearest = 0.01
CurRnd = Int(amt / nearest + 0.500001) * nearest
Else
CurRnd = 0
End If

Exit_CurRnd:
Exit Function


Err_CurRnd:
Select Case Err
Case 0

Case Else
MsgBox Err.Description
Resume Exit_CurRnd
End Select

End Function

While written for currency, it can be altered and used anywhere a rounding
function of this type is required. That would make the entire discussion
here moot.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

IIRC, Static variables have a scope equal to the life of the function,
unless they are declared at form or application wide level like a global
variable. The reason for using them is so the variable won't reinitialize.
Since the variable is called only once, and never reinitialized, there is
no advantage, in this particular case, to using a static variable. At the
global or even form level, unless this function is used repeatedly
throughout the session, Excel.exe stays in memory the entire time. That's
only efficient if the function is to be used in a query were it may be
called multiple times. Also, I was typing aircode and didn't bother to
clean up or use error handling. That too would make the function better.

 

[David W. Fenton]

IIRC, Static variables have a scope equal to the life of the
function, unless they are declared at form or application wide
level like a global variable.

Eh? Not sure what "equal to the life of the function" means. A
Static variable inside a function survives the call to the function,
which is the whole point of my using it. I initialize it once and
don't have to do so again, which with heavyweight startup processes,
like automating one of the other Office apps, or initializing a
database variable with CurrentDB, can be significant.

In the case of automating apps outside of Access, the startup
penalty can be quite large.

The reason for using them is so the variable won't reinitialize.
Eh?

Since the variable is called only once, and never reinitialized,
there is no advantage, in this particular case, to using a static
variable.

The advantage to me over a global is that it's declared directly in
the context in which it's used, and cannot be cleared except by code
operating within the function/sub in which it is declared.

At the
global or even form level, unless this function is used repeatedly
throughout the session, Excel.exe stays in memory the entire time.

There is no difference in terms of performance between the static
module-level variable and the one inside a function/sub. But in
terms of code manageability, there's a huge difference, in my
opinion.

That's
only efficient if the function is to be used in a query were it
may be called multiple times.

Bollocks. It's efficient even outside it. If you're using Outlook to
send email, you don't want to have to reload Outlook every single
time you send an email message -- the user *will* notice the
difference (assuming Outlook is not already loaded, of course). Or
Word or Excel or PDFCreator or whaterever.

Certainly, it seems to me if you're loading Excel for a function
from the Excel library, you could be very likely to use it in
contexts in which the pause to reload Excel would be quite
noticeable, even outside of a query or a loop.

On the other hand, if it's a function you use once in your app in a
component that's called only seldom, it's probably better to *not*
persist the Excel instance, because then you don't have to tear it
down when your app exits.

Also, I was typing aircode and didn't bother to clean
up or use error handling. That too would make the function better.

My comment was not intended as a criticism of what you posted.

 

[David W. Fenton]

The more I think about it. depending upon how the Ceiling ()
function is used, Excel doesn't even necessarily need to come into
the picture. In Excel, the Ceiling() function is generally used to
round up by a specified amount. More than 10 years ago, I wrote a
rounding function (there wasn't one in Access at the time) which
did just that:

That's fine for this particular function, but if you're using
multiple Excel functions, or using ones that it's hard to implement
so that they behave exactly like Excel's implementations (and you
need them to return results 100% identical to Excel's), then
automating Excel is completely justified.

For a simple rounding variant like this, I agree -- not so necessary
and probably overkill.

But that doesn't mean it's not something that is quite useful and
even necessary in other sitautions.

And my followup makes a point of segregating the process of
initializing Excel from the use of an Excel function. That is, there
are two different issues here, how to get the function and how to
manage automation of Excel. For the latter, I like creating a
function that represents the Excel object, and use a static variable
internally in that function to persist the Excel instance. Then that
object can be used anywhere, for whatever purpose, either to use an
Excel function, or to automate creation and population of an Excel
spreadsheet or graph, or whatever.

The discussion here has rather served to mix up the two issues when
they are quite distinct.