By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,561 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

Ceiling function

Ericks
P: 74
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.
Aug 25 '08 #1
Share this Question
Share on Google+
9 Replies


Expert 100+
P: 374
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:

Expand|Select|Wrap|Line Numbers
  1. Public Function Ceiling(RoundValue As Currency) As Currency
  2.     Dim TheValue As Currency
  3.     TheValue = RoundValue
  4.     Select Case TheValue - Int(TheValue)
  5.         Case Is <= 0.25
  6.             Ceiling = Int(TheValue)
  7.         Case Is >= 0.5
  8.             Ceiling = Int(TheValue) + 1
  9.         Case Is >= 0.26
  10.             Ceiling = Int(TheValue) + 0.5
  11.     End Select
  12. End Function
  13.  
  14.  
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.
Aug 25 '08 #2

Ericks
P: 74
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:

Expand|Select|Wrap|Line Numbers
  1. Public Function Ceiling(RoundValue As Currency) As Currency
  2.     Dim TheValue As Currency
  3.     TheValue = RoundValue
  4.     Select Case TheValue - Int(TheValue)
  5.         Case Is <= 0.25
  6.             Ceiling = Int(TheValue)
  7.         Case Is >= 0.5
  8.             Ceiling = Int(TheValue) + 1
  9.         Case Is >= 0.26
  10.             Ceiling = Int(TheValue) + 0.5
  11.     End Select
  12. End Function
  13.  
  14.  
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?
Aug 25 '08 #3

Expert Mod 2.5K+
P: 2,545
Hi. For whole numbers the following function implements a general approach to the 'round to nearest X' requirement:

Expand|Select|Wrap|Line Numbers
  1. Public Function RoundToNearestMultiple(lngWholeNoIn As Long, lngToNearest As Long) As Long
  2.     RoundToNearestMultiple = lngToNearest * Int(lngWholeNoIn / lngToNearest)
  3. End Function
Sample results:
Expand|Select|Wrap|Line Numbers
  1. X = RoundToNearestMultiple(80, 50) ==> X = 50
  2. X = RoundToNearestMultiple(2001, 50) ==> X = 2000
  3. X = RoundToNearestMultiple(2001, 100) ==> X = 2000
  4. X = RoundToNearestMultiple(2121, 100) ==> X = 2100
  5. X = RoundToNearestMultiple(2121, 20) ==> X = 2120
Aug 25 '08 #4

FishVal
Expert 2.5K+
P: 2,653
Expand|Select|Wrap|Line Numbers
  1. Public Function RoundToNearestMultiple(lngWholeNoIn As Long, lngToNearest As Long) As Long
  2.     RoundToNearestMultiple = lngToNearest * Int(lngWholeNoIn / lngToNearest)
  3. End Function
It would be better to use Round() function instead of Int().
Though it itself a little weird beast.

Regards,
Fish
Aug 25 '08 #5

missinglinq
Expert 2.5K+
P: 3,532
How about:

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

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

Linq ;0)>
Aug 25 '08 #6

Expert Mod 2.5K+
P: 2,545
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
Aug 25 '08 #7

Ericks
P: 74
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.
Aug 26 '08 #8

FishVal
Expert 2.5K+
P: 2,653
Even simpler:
Expand|Select|Wrap|Line Numbers
  1. -50*Int(-[TheValue]/50)
  2.  
Regards,
Fish

P.S. BTW I recall a thread there it was already discussed.
Aug 26 '08 #9

Ericks
P: 74
Even simpler:
Expand|Select|Wrap|Line Numbers
  1. -50*Int(-[TheValue]/50)
  2.  
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.
Aug 26 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.