CODEWITHSUNDEEP
sqlroundingms-access-2013
alextansc
alextansc

Reputation: 4672

How to ROUNDUP a number in Access 2013?

For Access 2013, I need a way to round up any fractional numbers to the next whole number in an SQL query.

Example: 

SELECT ROUNDUP(NumberValues) FROM Table1

In the above query, a row with 1.25 should return as 2 after querying.

As far as I know, there's no ROUNDUP function in Access 2013 for use in a SQL query statement.

Upvotes: 3

Views: 60202

Answers (8)

TenisViejos
TenisViejos

Reputation: 1

Public Function GblRoundUp(wNumber As Double, wDecPlaces As Integer) As Double
    Dim wResult As Double
    Dim wFactor As Double
    wFactor = -10 ^ wDecPlaces
    wResult = Int(wFactor * wNumber) / wFactor
    GblRoundUp = Round(wResult, wDecPlaces)
End Function

Upvotes: -2

benmakp benmakn
benmakp benmakn

Reputation: 61

all you need to do is to use round(field+0.004999,2) or round(field+0.000499,3) ...

Upvotes: -2

MikeJ
MikeJ

Reputation: 1

Example of the whole number round up:

If n > Round ( n, 0 ) then
    n = Round ( n, 0 ) + 1    
Else    
    n = Round ( n, 0 )    
End If

Upvotes: -2

Robert S.
Robert S.

Reputation: 19

Here is one that is simple and easy to understand:

Public Function roundUp(ByVal theValue As Long) As Integer
    Dim tempInt As Integer
    tempInt = theValue 'cast value to whole integer
    If (tempInt = theValue) Then 'check if original value was already whole
        'do nothing
    Else
        tempInt = tempInt + 1 'value was not whole integer, add one to round up
    End If
    roundUp = tempInt 'return rounded value
End Function

NOTE: Do not forget to check your value(s) for null before calling the function! This function will roundup the smallest to the largest decimal point to the next whole integer regardless!

Upvotes: -1

Stephanie C.
Stephanie C.

Reputation: 1

this works great as well 

Public Function roundUp(dValue As Double, idecimal As Integer) As Double
    Dim iSign As Integer
    If dValue < 0 Then
       iSign = -1
    Else
       iSign = 1
    End If
    dValue = Abs(dValue)

    If Round(dValue, 0) = 0 Then
        roundUp = 1 / 10 ^ idecimal * iSign
    Else
      roundUp = Round(dValue + 4 / 10 ^ (idecimal + 1), idecimal) * iSign

    End If

End Function

Example roundup(10.333,2)=10.34

Upvotes: -1

user7425513
user7425513

Reputation: 51

Excellent answer 'alextansc'. This little public function works a treat:

Public Function GlblRoundup(wNumber As Currency, wDecPlaces As Integer)  As Currency

Dim wResult As Currency
Dim wFactor As Currency

    Select Case wDecPlaces
        Case 0
            wFactor = -1
        Case 1
            wFactor = -10
        Case 2
            wFactor = -100
        Case 3
            wFactor = -1000
        Case 4
            wFactor = -10000
        Case Else
            wFactor = -10000
    End Select

    wResult = Int(wFactor * wNumber) / wFactor

    GlblRoundup = Round(wResult, wDecPlaces)

End Function

Upvotes: 1

Felix
Felix

Reputation: 59

I have found the easiest way to round up a number in access is to use the round function like this:

Round([MyField]+0.4,0)

The number 10.1, for example then becomes 10.5. When the round function is applied, it rounds up to 11. If the number is 10.9, adding 0.4 becomes 11.3, which rounds to 11.

Upvotes: 5

alextansc
alextansc

Reputation: 4672

I found a ROUNDUP equivalent from this link: http://allenbrowne.com/round.html#RoundUp

To round upwards towards the next highest number, take advantage of the way Int() rounds negative numbers downwards, like this: - Int( - [MyField])

As shown above, Int(-2.1) rounds down to -3. Therefore this expression rounds 2.1 up to 3.

To round up to the higher cent, multiply by -100, round, and divide by -100: Int(-100 * [MyField]) / -100

The syntax is counter-intuitive, but it works exactly as I intended.

Upvotes: 9

Related Questions