correct way to round up an integer after division

[ChasW]

I am not sure if there is a VBA function to simplify this.

What is a good way to correctly round up to the nearest whole number
after dividing?

for example 135 / 10 = 13.5
for this result, 14 is the desired value for a SELECT TOP

another example 133 / 10 = 13.3
for this result, 14 still is the desired value for the SELECT TOP
query.

Regards,
Charles

 

[Marshall Barton]

I am not sure if there is a VBA function to simplify this.

What is a good way to correctly round up to the nearest whole number
after dividing?

for example 135 / 10 = 13.5
for this result, 14 is the desired value for a SELECT TOP

another example 133 / 10 = 13.3
for this result, 14 still is the desired value for the SELECT TOP
query.

Technically, that's not rounding, it's called the ceiling
function. Unfortunately, Access does not have that built
in, so you you need to create your own:

Public Function Ceiling(x As Double) As Double
Ceiling = Fix(x) + IIf(x = Fix(x), 0, Sgn(x))
End Function

Place that in a standard module so you can use it anywhere
in your application.

Note: Be careful of some calculations with Single or Double
type numbers. Computers can not always represent these
types of numbers exactly so you may find the function is not
quite what you expect. I.e. if the imprecision is as much
as .00000001, it may be enough throw the calculation off by
1. For example:
CDbl(1) - CDbl(1/3) - CDbl(2/3) = .000000000000000055511151
which is not quite zero.

If you are working with numbers that are no more than 4
places, consider using Currency instead of Double.

 

[Nick 'The database Guy']

Hi Chas, Marshall,

The ceiling fucntion is quite elegant, however I am concerned that
possibly chas will want a different result, ie not 14, if the answer
would have been 13.1?

Thanks