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
+ 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

 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
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 Public Function Ceiling(RoundValue 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     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

 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 Public Function Ceiling(RoundValue 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     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 Public Function RoundToNearestMultiple(lngWholeNoIn As Long, lngToNearest As Long) As Long     RoundToNearestMultiple = lngToNearest * Int(lngWholeNoIn / lngToNearest) End Function Sample results: Expand|Select|Wrap|Line Numbers 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 Aug 25 '08 #4

 Expert 2.5K+ P: 2,653 Expand|Select|Wrap|Line Numbers 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 Aug 25 '08 #5

 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

 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

 Expert 2.5K+ P: 2,653 Even simpler: Expand|Select|Wrap|Line Numbers -50*Int(-[TheValue]/50)   Regards, Fish P.S. BTW I recall a thread there it was already discussed. Aug 26 '08 #9

 P: 74 Even simpler: Expand|Select|Wrap|Line Numbers -50*Int(-[TheValue]/50)   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.