Rounding decimals in a math operation

Occasional Visitor

When performing a arithmetic operation in an Access field, such as
I want a code that rounds this result to 3
meaning nearest and highest integer
thank you

4 Replies



You can e.g. use the Format function like this: Format(Whatever, "#")


There are also many user defined Rounding functions like the one on my website (in German, but the code works for other languages too ;-).



As Karl suggested there are many ways of doing this, jncludjng formatting.

Other methods using built in functions include


Int and Fix both give the integer value so you need to add 1 to the result


But what if the result is nearer to the lower integer value e.g. 2

Both CInt and Round will now round down so you would need to add 1. 

So avoid those as not consistent.


Now consider negative results such as -2.56 or -2.16

Assuming you want the result to be -2 in each case, then the only function that always gives the desired answer in all scenarios is using Int(number)+1


Hope that makes sense


... as the nearest integer could be lower, I guess you mean "nearest higher integer".

That is rounding up which is easy to perform:

ValueUp = -Int(-(5*5*4/3+2*5+2*4)/20)
' ValueUp will be 3.

 For serious rounding of any values with extreme precision, go to VBA.Round .

Interesting. So both of these expressions round up to the next higher integer for all positive and negative numbers

However if the result is itself an integer, adding 1 will give the wrong result.
So I agree that that using Gustav's method is better.