Ceiling function

[Ericks]

I have a calculated field in a report that states: =Sum([Product Need])
How can I have it round up to the nearest 50. In Excel I use (ceiling(cel;50 )). How would that be in Access? I tried =ceiling(Sum([Product Need]);50) but that doesn't work.

[PianoMan64]

I have a calculated field in a report that states: =Sum([Product Need])
How can I have it round up to the nearest 50. In Excel I use (ceiling(cel;50 )). How would that be in Access? I tried =ceiling(Sum([Product Need]);50) but that doesn't work.

You would creating a function that would do the same thing as ceiling in Excel.

But since you only need it to round to the closes 50 cents, then you would simply leave out the Significance options out the function.

for Example:

[code=VB]
Public Function Ceiling(RoundVa lue As Currency) As Currency
Dim TheValue As Currency
TheValue = RoundValue
Select Case TheValue - Int(TheValue)
Case Is <= 0.25
Ceiling = Int(TheValue)
Case Is >= 0.5
Ceiling = Int(TheValue) + 1
Case Is >= 0.26
Ceiling = Int(TheValue) + 0.5
End Select
End Function

[/code]

Then you would copy and paste this into a new module that will hold this public function that you can use anywhere in your application.

If you need any more help, let me know,

Hope that helps,

Joe P.

[Ericks]

You would creating a function that would do the same thing as ceiling in Excel.

But since you only need it to round to the closes 50 cents, then you would simply leave out the Significance options out the function.

for Example:

[code=VB]
Public Function Ceiling(RoundVa lue As Currency) As Currency
Dim TheValue As Currency
TheValue = RoundValue
Select Case TheValue - Int(TheValue)
Case Is <= 0.25
Ceiling = Int(TheValue)
Case Is >= 0.5
Ceiling = Int(TheValue) + 1
Case Is >= 0.26
Ceiling = Int(TheValue) + 0.5
End Select
End Function

[/code]

Then you would copy and paste this into a new module that will hold this public function that you can use anywhere in your application.

If you need any more help, let me know,

Hope that helps,

Joe P.

Thanks Joe. Actually, it's for whole numbers like 21, 43, 95, 110, etc. that have to be rounded like respectively 50, 50, 100 and 150.
Does that mean I should adapt your code by writing for example 25 instead of 0.25?

[Stewart Ross]

Hi. For whole numbers the following function implements a general approach to the 'round to nearest X' requirement:

Public Function RoundToNearestMultiple(lngWholeNoIn As Long, lngToNearest As Long) As Long
    RoundToNearestMultiple = lngToNearest * Int(lngWholeNoIn / lngToNearest)
End Function

Sample results:

X = RoundToNearestMultiple(80, 50) ==> X = 50
X = RoundToNearestMultiple(2001, 50) ==> X = 2000
X = RoundToNearestMultiple(2001, 100) ==> X = 2000
X = RoundToNearestMultiple(2121, 100) ==> X = 2100
X = RoundToNearestMultiple(2121, 20) ==> X = 2120

[FishVal]

Public Function RoundToNearestMultiple(lngWholeNoIn As Long, lngToNearest As Long) As Long
    RoundToNearestMultiple = lngToNearest * Int(lngWholeNoIn / lngToNearest)
End Function

It would be better to use Round() function instead of Int().
Though it itself a little weird beast.

Regards,
Fish

[missinglinq]

How about:

iif([TheValue] mod 50=0, [TheValue], [TheValue]\50)*50 + 50)

21 = 50
43 = 50
95 = 100
110 = 150

Linq ;0)>

[Stewart Ross]

Hi all. Sometimes I just catch myself completely missing the silliest things - such as the need to round up the value. How did I manage to miss that? Easy modulo arithmetic - but an incorrect result. Dear oh dear...

Anyway, thanks guys for spotting my mistook.

-Stewart

[Ericks]

How about:

iif([TheValue] mod 50=0, [TheValue], [TheValue]\50)*50 + 50)

21 = 50
43 = 50
95 = 100
110 = 150

Linq ;0)>

Exciting discussion. I'm currently traveling but will implement the suggestion ASAP. Will get back to you guys.

Thanks a bunch.

[FishVal]

Even simpler:
[code=vb]
-50*Int(-[TheValue]/50)
[/code]

Regards,
Fish

P.S. BTW I recall a thread there it was already discussed.

[Ericks]

Even simpler:
[code=vb]
-50*Int(-[TheValue]/50)
[/code]

Regards,
Fish

P.S. BTW I recall a thread there it was already discussed.

OK, I used this code in my report and it works. Fantastic. Thanks a lot for this.