How do you round to the next integer in Access 2000?

[Guest]

I have been searching for a formula or module that will allow me to round a
number up.

Let me give a couple examples of what I need to do...

I have 35 parts to ship and I can fit 30 in a box. Therefore I would need 2
boxes to ship the parts. I need a way to force access in to rounding
1.166...up to 2.

At the same time I could have 29 parts to ship that fit 30 to a box. So I
would only need 1 box this time and still need to have 0.966...rounded up to
1 (which access will do normally). Any help on this would be greatly
appreciated.

 

[Guest]

IIf(35/30 = Int(35/30), 35/30, Int(35/30) + 1)

IIf([Parts]/[Boxes] = Int([Parts]/[Boxes]), [Parts]/[Boxes],
Int([Parts]/[Boxes]) + 1)

Just be double sure not to divide by 0 or have nulls in any of the numbers.

 

[Guest]

Thank you for this information it worked wonderfully and should help out a
ton with the reporting I'm doing.

 

[Guest]

Thank you for the information and from what I see it's a fairly simple
formula. But there was a reponce before yours that works for me much better.
Again thank you.

IIf(35/30 = Int(35/30), 35/30, Int(35/30) + 1)

IIf([Parts]/[Boxes] = Int([Parts]/[Boxes]), [Parts]/[Boxes],
Int([Parts]/[Boxes]) + 1)

Just be double sure not to divide by 0 or have nulls in any of the numbers.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have been searching for a formula or module that will allow me to round a
number up.

Let me give a couple examples of what I need to do...

I have 35 parts to ship and I can fit 30 in a box. Therefore I would need 2
boxes to ship the parts. I need a way to force access in to rounding
1.166...up to 2.

At the same time I could have 29 parts to ship that fit 30 to a box. So I
would only need 1 box this time and still need to have 0.966...rounded up to
1 (which access will do normally). Any help on this would be greatly
appreciated.

 

[Guest]

The following function rounds up to the next integer and allows you to
specify the number of significant decimal places for rounding:

Function RoundUp(dblNum As Double, intDecs As Integer) As Long

' rounds up to integer taking account of specified number
' of decimal places

Dim lngNum As Long

lngNum = Int(dblNum)

On Error Resume Next
dblNum = lngNum & Mid(dblNum, InStr(dblNum, "."), intDecs + 1)
On Error GoTo 0

If lngNum <> dblNum Then
RoundUp = Int(dblNum + 1)
Else
RoundUp = lngNum
End If

End Function

For instance:

RoundUp(1.066,1) returns 1

but:

RoundUp(1.066,2) returns 2

In your case, with 30 as the divisor 2 significant decimal places should do,
but using an artificially higher number as the second argument will work just
as well and cover all possibilities if a higher divisor, i.e. number in a
box, is used.

Ken Sheridan
Stafford, England