asked on

MS Access use of Ceiling() function to round up and handle negative numbers similarly to positive

I'm using:

Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
    ' X is the value you want to round
    ' is the multiple to which you want to round
    Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) * Factor
End Function

Open in new window

but -1.28 is rounded to -1

what I need is -1.28 to round to -2

I also need 1.28 to continue to round to 2 (as it does now)

eventually, in another system that rounds up I'm having to match -1.28 to 1.28

Randy Poole

Try this

Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
    ' X is the value you want to round
    ' is the multiple to which you want to round
    boolean neg=X<0
    X=ABS(X)
    Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) * Factor
    if (neg) then Ceiling =-(Ceiling )
End Function

Open in new window

ghettocounselor

ASKER

getting a Compile Error: Syntax Error with that.

The "boolean neg=X<0" bit is in RED if that is on any use.

I'm in Access 2007 sorry not to have noted that previously if useful.

ASKER CERTIFIED SOLUTION

Randy Poole

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Randy Poole

Sorry, I have been shuffling around in so many languages today I forget which syntax goes to which :P

ghettocounselor

ASKER

Beautiful!
Thanks as always for the attention.

Gustav Brock

That is a quite complicated way to achive this when a one-liner will do:

intRounded = -Sgn(intValue) * Int(-intValue * Sgn(intValue))

/gustav