Increment Rounding

[Exodus]

I need to round numbers up to the nearest 50

IE
80 = 100
110 = 150
1 = 50

I'm using this code and am get mostly the desired results except some
80 = 150

What can I do to fix this.

Im calling it in a query like this
PadQty: RoundToNearest([Qty],50)

Function RoundToNearest(dblNumber As Double, varRoundAmount As Double, _
   Optional varUp As Variant) As Double
            
   Dim dblTemp As Double
   Dim lngTemp As Long
      
   dblTemp = dblNumber / varRoundAmount
   lngTemp = CLng(dblTemp)
      
   If lngTemp = dblTemp Then
         RoundToNearest = dblNumber
         Else
         ' round up
         dblTemp = lngTemp + 1
      
      RoundToNearest = dblTemp * varRoundAmount
   End If
End Function

 

[flomar]

Answer

Hi,

your function is working fine like this.

Remarks:
Input_DBL ... Input-Value
RoundVal_INT ... RoundUpTo-Value (Standard = 50)

\ ... Used to divide two numbers and return an integer result.
/ ... Used to divide two numbers and return a floating-point result.

Maybe it would be nicer to use the function "mod" to calculate the rest, but as it works fine

Regards, Florian.


******************************************
Function RoundUp(Input_DBL As Double, Optional RoundVal_INT As Integer) As Integer

Dim Calc_INT As Integer
Dim CorrectVAL_INT As Integer

'Set all variables to 0
RoundUp = 0
CorrectVAL_INT = 0

'Set standard value for roundup_int to 50 if no value is set
If RoundVal_INT = 0 Then RoundVal_INT = 50

Calc_INT = Input_DBL \ RoundVal_INT
'Calculate if Value has to be corrected upwards
If (Input_DBL / RoundVal_INT) - (Input_DBL \ RoundVal_INT) > 0 Then CorrectVAL_INT = RoundVal_INT

'Calculate the final value
RoundUp = Calc_INT * RoundVal_INT + CorrectVAL_INT

End Function

 

[sfreeman@co.mer]

I think I see the problem, too many words...



Just kidding, the above works fine, but the mod suggestion is a good one.

Try this:

Function RoundToNearest2(dblNumber As Double, varRoundAmount As Double, _
Optional varUp As Variant) As Double

RoundToNearest2 = dblNumber
If dblNumber Mod varRoundAmount <> 0 Then RoundToNearest2 = dblNumber + (varRoundAmount - (dblNumber Mod varRoundAmount))

End Function

Testing with this SQL:

'********************************************
SELECT
tblRound.Number
, tblRound.RoundTo
, RoundToNearest2([Number],[RoundTo])

AS
Rounded

FROM
tblRound;
'********************************************

gives this dataset:

Number RoundTo Rounded
1 50 50
2 50 50
3 50 50
4 50 50
5 50 50
6 50 50
7 50 50
8 50 50
9 50 50
10 50 50
11 50 50
12 50 50
13 50 50
14 50 50
15 50 50
16 50 50
17 50 50
18 50 50
19 50 50
20 50 50
21 50 50
22 50 50
23 50 50
24 50 50
25 50 50
26 50 50
27 50 50
28 50 50
29 50 50
30 50 50
31 50 50
32 50 50
33 50 50
34 50 50
35 50 50
36 50 50
37 50 50
38 50 50
39 50 50
40 50 50
41 50 50
42 50 50
43 50 50
44 50 50
45 50 50
46 50 50
47 50 50
48 50 50
49 50 50
50 50 50
60 50 100
70 50 100
80 50 100
90 50 100
100 50 100
125 50 150
150 50 150
175 50 200
200 50 200
250 50 250
300 50 300
450 50 450
500 50 500
1 100 100
2 100 100
3 100 100
4 100 100
5 100 100
6 100 100
7 100 100
8 100 100
9 100 100
10 100 100
11 100 100
12 100 100
13 100 100
14 100 100
15 100 100
16 100 100
17 100 100
18 100 100
19 100 100
20 100 100
21 100 100
22 100 100
23 100 100
24 100 100
25 100 100
26 100 100
27 100 100
28 100 100
29 100 100
30 100 100
31 100 100
32 100 100
33 100 100
34 100 100
35 100 100
36 100 100
37 100 100
38 100 100
39 100 100
40 100 100
41 100 100
42 100 100
43 100 100
44 100 100
45 100 100
46 100 100
47 100 100
48 100 100
49 100 100
50 100 100
60 100 100
70 100 100
80 100 100
90 100 100
100 100 100
125 100 200
150 100 200
175 100 200
200 100 200
250 100 300
300 100 300
450 100 500
500 100 500

Does this meet your spec?

HTH

 

[Exodus]

Thanks for your help guys
I really appreciate it.
flomar's funtion worked as is.

 

[ByteMyzer]

You could save yourself even more code if you use the following Ceiling Function:

Function Ceiling(ByVal X As Double, Optional ByVal Factor As Double = 1) _
    As Double
    Ceiling = (X \ Factor - (X / Factor - X \ Factor > 0)) * Factor
End Function

?Ceiling(47,50)
...will return...
50

?Ceiling(201,50)
...will return...
250

It also works well with Decimal increments:

?Ceiling(13,14.3)
...will return...
14.3

?Ceiling(15,14.3)
...will return...
28.6

 

[Exodus]

Thanks for your Help I appriciate it.

 

[BLeslie88]

Thank You!

I NEEDED CEILING FUNCTION U GUYS ARE THE BEST!

It existed in excel, and I needed it in access for bar coding upc formula to get cehck digit... You guys rule...