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.

example

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

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.

HTH
Noah

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

Crystal,

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

ooh! you are right! 0.04999 should be added, not 0.5! Thanks!

CRYSTAL !!!

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

?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

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

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