Increase a number so it will devide by 6 1

[seke]

I'm making a database which is for time billing.

a billing unit is 6 min. So if the total time taken is 15 min
this would be rounded up to 18 min or 3 x 6 min units.

The 15 min would be entered in one field. The number of rounded units to appear in a second field on tab out of first.

I thank in advance anyone who can help.

I have searched the forum to no avail

 

[Terpsfan]

You could create the standard billing unit as a constant.

Private Const conBillingUnit as integer = 6

Create a field for total billing minutes (txtBillingMinutes). Then another text box for total billing units with this function txtBillingMinutes/conBillingUnit, formatting this text box as fixed with 0 decimal places.

 

[seke]

thank you Omega36. I had the same idea but it only works in part.

example
If you enter 13 min it rounds down to 12. 2 x 6 min units where I need it to always round up. So 13 min sould be 3 x 6 min units 18 min.

Thanks for the idea anyway

 

[Terpsfan]

Ok, my bad...then use the mod function.

Private Const conBillingUnit as integer = 6

If txtBillingMinutes Mod conBillingUnit > 0 Then
txtBillingUnits = (txtBillingMinutes\conBillingUnit) + 1
Else
txtBillingUnits = txtBillingMinutes/conBillingUnit
End If

 

[missinglinq]

Seke,

Basically what you need to do is use the MOD operator to test your ActualMinutes and see if they are evenly divisible by 6. The MOD operator returns only the Remainder of a division operation.

Thus if your ActualMinutes are 12, 12 MOD 6 = 0, and your
ActualMinutes are evenly divisible by 6 and your Billing Minutes = your ActualMinutes.

If, however, your ActualMinutes are 13, 13 MOD 6 = .16666666 (which is > 0), so your ActualMinutes are not evenly divisible by 6, and your Billing Minutes <> your ActualMinutes.

In this case you must then increment your ActualMinutes by 1
(ActualMinutes = ActualMinutes + 1) and run the test again.

You repeat this proceedure until ActualMinutes MOD 6 = 0, and then your Billing Minutes = your ActualMinutes. You then exit the proceedure.

I haven't tested this sub, but I'm pretty sure the syntax is correct.
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Sub CalculateBillingTime()
Dim ActualMinutes As Integer
Dim BillingMinutes As Integer

Test1:
If ActualMinutes MOD 6 = 0 Then 'AMinutes divisible by 6
BillingMinutes = ActualMinutes
Goto LeaveFunction
End If

Test2:
If ActualMinutes MOD 6 > 0 Then 'Problem not solved yet
ActualMinutes = ActualMinutes +1
Goto Test1
End If

LeaveFunction:
'Problem solved, now exit the Sub now

End Sub
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

I know it's complicated, but that's what you get for charging people for more work or calling time than you're actually delivering! LOL!

Hope this points you in the right direction. Check the syntax carefully (I've been up 28 hours and am feeling a little fuzzy!)

The Missinglinq

&quot;It's got to be the going,
not the getting there that's good!&quot;
-Harry Chapin

 

[seke]

Omega36 & Missingling

Thanks for your input, time & help

Found the answer:

Me!result = (Me!min + IIf(Me!min Mod 6 > 0, (6 - Me!min Mod 6), 0)) / 6

Again thanks

Seke

 

[seke]

Now we all know why the legal profession make so much money
if it takes 7 mins you get billed for 12.

I think i will use the same method when I send his bill.

Seke

 

[missinglinq]

Seke,

There's always more than one way to skin a cat, and yours was a very elegant one ! I've never used the "IIF" construct (I'm used to using the functions of the VB ancestor, as my answer shows) and am happy to add it to my arsenal of tricks!

Looking at my answer and yours shows one of the great things about VBA; MS turned a great deal of onerous tasks into simple
no-brainers! It used to take a page of code to add 8 weeks to a current date to get a future date; now it takes a line!

Thanks for a new trick!

The Missinglinq

&quot;It's got to be the going,
not the getting there that's good!&quot;
-Harry Chapin

 

[Rolliee]

This will do it also:
var1 = me.text0 Mod 6
Me.Text0 = Me.Text0 + IIf((var1) > 0, 6 - var1, 0)

the use of a variable is a trade of speed for memory that old programmers use. It minimizes the calculations and does not require a DIV.

rollie@bwsys.net

 

[PHV]

And what about this ?
Me!result = (Me!min + 5) \ 6


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884