Mar 20 2004, 12:33 AM
This is for time billing
I need to enter the number of mins in one field. the round up unit in a second field in this case 6 min, the result displayed in a third field.
1 unit = 6 min
13min devide by 6 = 3 units ie 18 min
The number must always round up
Any Help welcome and thanks in advance
Mar 20 2004, 12:49 AM
Welcome to Utter Access!
You will not want to store this number. Calculate it as needed in an unbound text box with:
=([yournumberofminutesfield]+IIf([yournumberofminutesfield] Mod 6>0,(6-[yournumberofminutesfield] Mod 6),0))/6
What this does is to take the number of minutes and, if there is a remainder when that number is divided by 6 (found using the Mod function), then it adds the difference to bring the number up to next closest multiple of 6 and then divides the number by 6 to get your units.
Mar 20 2004, 10:23 AM
may I offer another solution?
Since you have no care for what the remainder is, you can also do it this way:
= cInt( [yournumberofminutesfield]/6 + 0.5 )
this converts the number divided by 6 to an integer. It adds 0.5 first so it will always round up.
if your numbers will be greater then 32,000, use the cLng function instead of cInt
Mar 20 2004, 03:14 PM
Problem with your code (I believe).
It works fine given the example of the questioner (i.e. 13 mins), and does it for 14 thru 17.
But when it hits 18 minutes, your code returns 4 (which I don't think is the req'd result).
Of course 12 also gives 3, which again is not right.
Noah's does it correctly because it determines if ( ? ) there is a reult by the division BEFORE adjusting
Mar 20 2004, 06:35 PM
ooh! you are right! 0.04999 should be added, not 0.5! Thanks!
Mar 20 2004, 07:03 PM
.... and THAT (adding 0.04999 instead of (your) original 0.5) does NOT work for the numbers 13, 14 (both return 2) , 19, 20 ( both return 3) etc etc
Again, Noah's is the way to go
Mar 20 2004, 07:15 PM
?cInt( 14/6 + 0.4999 ) = 3
?cInt( 13/6 + 0.4999 ) = 3
?cInt( 13/6 + 0.4999 ) = 2
Oh, I see I messed up again (terrible headache today -- I am off by a decimal place in my explanation. I should have just stayed in bed)
0.04999 should be added, not 0.5! Thanks!
pardon me 0.49999
I have used this method a lot myself. Noah's method works too. They both do -- as long as these silly zeros don't end up in the wrong places
Mar 20 2004, 07:20 PM
... and I didn't look back to see that your original had said "0.5" which would have indicated to me (maybe) that you were "off" by a decimal point.
Point (and no pun intended) taken ...........
Mar 20 2004, 08:44 PM
that's okay --
I think this post hits my record for errors (not that I am trying to set one...)
?cInt( 13/6 + 0.4999 ) = 2 SHOULD be
?cInt( 12/6 + 0.4999 ) = 2
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here