Rounding decimal places to 2

[Oscar]

No doubt this is simple! I have numbers in my database which have a large number of decimal places and I would like to change these to two decimal places, with rounding. I figured out how to just chop the numbers off but cant get it to round. I found a mathematical function in a book, CEIL(X), which says it can be used for rounding. It however doesn't show you an example and I can't figure out how to use it. It doesn't appear under the builder functions! Some help would be much appreciated.

 

[Rich]

Paste this code into a standard module
Public Function Round2CB(Value, Optional Precision As Variant) As Double

If IsNull(Value) Then Exit Function
If IsMissing(Precision) Then Precision = 2
Value = Fix(Value * 10 ^ Precision + 0.5 * Sgn(Value)) / 10 ^ Precision
Round2CB = Value

End Function

To use it anywhere in your db just add =Round2CB([YourField])

 

[Oscar]

Thanks

Thanks heaps, works a treat. Would love to know how that calculation works ie Value = Fix.....

Would never have figured this out on my own.