Access Equivalent to Excel's Roundup 2

[edsearl]

Is there a Access equivalent to Excel's Roundup?

 

[greely]

I don't believe access provides a RoundUp function like Excel.

I developed a function some time ago which is similar in functionality, I call it RoundItUp:

==============
Function roundItUp(num As Double, dig As Integer)
Dim nmb, dg, mult

if dig<=0 then
msgbox "The decimal places to be rounded must be greater than 0",vbokonly,"Error"
exit function
end if

If num = 0 Then
roundItUp = 0
Else
If (num < 0) Then
mult = -1
Else
mult = 1
End If

nmb = Abs(num)
dg = dig

nmb = nmb * 10 ^ dg
If (Abs(num) * (10 ^ (dig + 1))) Mod 10 = 0 Then
nmb = Fix(nmb)
Else
nmb = Fix(nmb) + 1
End If

roundItUp = (nmb / (10 ^ dg)) * mult
End If
End Function
==================

Limitations:

This function will 'roundup' negative numbers as well as positive numbers. However, unlike RoundUp, it will not calculate properly with negative numbers in the 'dig' spot. 'dig' must be greater than 0.

Additionally, if the number has no decimal digits or if 'dig' is greater than the number of decimal digits in the number, the number returned will be the number itself. Of course, use at your own risk.

Samples:
rounditup(28.8245,1) returns 28.9
rounditup(28.8245,2) returns 28.83
rounditup(28.8245,3) returns 28.825
rounditup(28.8245,4) returns 28.8245
rounditup(28.8245,5) returns 28.8245
rounditup(-28.8245,3) returns -28.825
rounditup(28.82,2) returns 28.82
rounditup(28.82,3) returns 28.82
rounditup(28.82,4) returns 28.82
rounditup(-28.82,2) returns -28.82
rounditup(-28.82,3) returns -28.82

Hope this helps.

 

[GingerR]

I use this to round to two decimal places:

(int(((FieldNameOrCalculationHere)+.005)*100))/100

...to one decimal place

(int(((FieldNameOrCalculationHere)+.05)*10))/10

...and so on, just adding or removing zeros in the last three numbers.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html