I would like to have a function return an integer which give me
ceiling result.
For example, I need get result "1", for any number from 0.001 to 0.999
and get result "2" for any number from 1.001 to 1.999

I tried to use Int, Round which trunc the number after decimal.

Your help is great appreciated,

On Fri, 25 Jun 2010 07:11:16 -0700 (PDT), iccsi <(E-Mail Removed)> wrote:

>I would like to have a function return an integer which give me
>ceiling result.
>For example, I need get result "1", for any number from 0.001 to 0.999
>and get result "2" for any number from 1.001 to 1.999
>
>I tried to use Int, Round which trunc the number after decimal.
>
>Your help is great appreciated,
>

The Int() function rounds down, even for negative numbers - so negate your
number, use int, and negate the result:

-Int(-[yournumberfield])

--

John W. Vinson [MVP]

"Arvin Meyer" <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> One of the beauties of Office automation is that you can use
> functions from other Office products. While I could write custom
> code for an Access Ceiling function, I prefer to use Excel's, like
> this:
>
> 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

I'd suggest using a static variable inside that function, and
checking if it Is Nothing, and only then initializating it. That
way, you won't be starting up Excel every time you call it (and
you're not shutting it down, so you'd end up with a bunch of
orphaned invisible instances of Excel with your code, no?). The code
should probably also have an optional flag to tear down Excel, so
I'd probably write it something like this (though it needs error
handling):

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

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

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

"Arvin Meyer" <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> Before going through all that, I'd use the solution Marsh Barton
> posted. It's more elegant than opening Excel anyway.

Certainly for this particular function. But for more complex Excel
functions, automating Excel is better since you are guaranteed of
the same result, rather than running the risk of implementing
something slightly different in Access.

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