Help with rounding up to nearest 20

[Sean James]

I have been searching the forum for the last hour or so. I'm new to access, vba and macros. So I asking for some help, please be gentle with me. I'm am trying to round up the the nearest twenty.

example. I have a table that contains an estimate take-off f and I want to round the pipe footage to the nearest 20 for purchase amounts and update the table.

ie.

Item QTY
Pipe 534.08
Pipe 405.5
Pipe 28.83
Pipe 1381

Item QTY
Pipe 540
Pipe 420
Pipe 40
Pipe 1400


Another problem I'm not sure how to fix is, some of the qty totals are two decimals and others are nine. Maybe not an issue but thought it might be.

So if anyone is compelled to help a greenie out it would be much appreciated.

Thanks

 

[EdFred]

Qty = (Fix(qty/20)+1) * 20

(note - will not work with exactly 20' cuts.)
If you wanted it to work with exactly 20' cuts then you would need to put an if statement in..

If Fix(Qty/20) <> (Qty/20) then
Qty = (Fix(qty/20)+1) * 20
End If

 

[Sean James]

"I see you beat me to the punch. -- Thanks"

Thanks for the help.

It worked on some records but not all.

534.083 became 560 should be 540
32 became 60 should be 40
77.3 became 100 should be 80
317 became 340 should be 320
393.916 became 420 should be 400
1358.0833 became 1380 should be 1360
253.333 became 280 should be 260

any idea on how to correct it?

 

[Sean James]

I'm confused what is "Fix"?

If Fix(Qty/20) <> (Qty/20) then
Qty = (Fix(qty/20)+1) * 20
End If

 

[RuralGuy]

How about:

Function Round20(InLength As Single) As Integer

If (InLength Mod 20) <> 0 Then
   Round20 = ((InLength \ 20) * 20) + 20
Else
   Round20 = InLength
End If

End Function

 

[pbaldy]

Or

http://support.microsoft.com/kb/209996/en-us

 

[EdFred]

I'm confused what is "Fix"?

Fix strips off the decimal places.

So using the Fix instead of Cint with 35 you would get:

35/20 = 1.75
Fix(1.75) = 1
1+1 = 2
2 * 20 = 40

Using Cint it rounds up so you would get
35/20=1.75
CINT(1.75) = 2
2+1 = 3
3 * 20 = 60