[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-07-10
6
Medium Priority
?
1,661 Views
Last Modified: 2014-07-10
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
0
Comment
Question by:ghettocounselor
  • 3
  • 2
6 Comments
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40188817
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

0
 

Author Comment

by:ghettocounselor
ID: 40188844
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.
0
 
LVL 21

Accepted Solution

by:
Randy Poole earned 2000 total points
ID: 40188851
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
    Dim neg as 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


Try that
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 21

Expert Comment

by:Randy Poole
ID: 40188853
Sorry, I have been shuffling around in so many languages today I forget which syntax goes to which :P
0
 

Author Closing Comment

by:ghettocounselor
ID: 40188967
Beautiful!
Thanks as always for the attention.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40189097
That is a quite complicated way to achive this when a one-liner will do:

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

/gustav
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question