Calculating hours and minutes

[richbneal]

I really like the site so far and this is my first post. I have looked through some of the archives with no luck. I have also read the posting guidelines and will do my best to be clear and accurate with my problem. I am working with Access 2k / Win 2k. I have some experience and must have this figured out before Aug1.

What i am tasked with is to modify an existing process that calculates the hours and minutes worked, which is entered on a form using controls bound to fields in a table with simple functions running the calculations.
The initial process had 7 text boxes for data entry.
Each control had both hours and minutes entered, example: 7.2, and called the function

 Public Function OC_Add_Hours()
'Dim ri, pfh, tth, pht, ht, postht, at, th As Variant
 
'ri = Forms![attorney statements]![releasee interview]
'pfh = Forms![attorney statements]![prep for hrg]
'tth = Forms![attorney statements]![travel to hrg]
'pht = Forms![attorney statements]![pre hrg time]
'ht = Forms![attorney statements]![hear time]
'postht = Forms![attorney statements]![post hrg time]
'at = Forms![attorney statements]![appeals time]
'th = Forms![Attorney Statements]![total hours]
 
'th = ri + pfh + tth + pht + ht + postht + at 
 
'Forms![attorney statements]![total hours] = th
End Function 

on the exit property.

In the function, th = "total hours" and this control holds the result. The data entered comes from a form sent in from clients with the time they spent working on a case. This time is currently being converted by the person entering the data using a conversion table showing,
"1 = 2 all the way up to 60 = 100". An example on the sheet states, Total hours worked is 6hrs 20 min. The conversion will be 6.33. This confused me!!

They want to get rid of this conversion and just calculate the time by 60 instead of 100.
What I did is separate the hours from the min by creating 7 new controls to capture the minutes. The new form now has the original control "releasee interview" for the hours worked entry and the new control "Rel Interview Min" for the minutes worked capture. I followed this process for all 7 controls. I am no loner calling the hours function and created the routine,

 
Private Sub Rel_Interview_Min_AfterUpdate()
Dim ri, rim, rit  As Long
 
ri = Forms![attorney statements]![releasee interview]
rim = Forms![attorney statements]![Rel Interview Min]
'rit = Forms![attorney statements]![Rel Interview Total]
 
rit = ri * 60 + rim
Forms![attorney statements]![Rel Interview Total] = rit
End Sub

to run in the AfterUpdate property of all of the minute controls.
Then I created seven controls that will not be visible, using the same naming style, except they end with total to hold this result.
My thinking is to change everything into minutes to be held in the Total min controls and then in the "total hours" control, in the "On Enter" property run this routine:

 Private Sub total_hours_Enter()
Dim rit, pfht, ttht, phtt, htt, pohtt, att, th As Long
 
rit = Forms![attorney statements]![Rel Interview Total]
pfht = Forms![attorney statements]![Prep for Hrg Total]
ttht = Forms![attorney statements]![Travel to Hrg Total]
phtt = Forms![attorney statements]![Pre Hrg Time total]
htt = Forms![attorney statements]![Hrg Time Total]
pohtt = Forms![attorney statements]![Post Hrg Time Total]
att = Forms![attorney statements]![Appeals Time Total]
'th = Forms![attorney statements]![total hours]
 
th = rit + pfht + ttht + phtt + htt + pohtt + att
Forms![attorney statements]![total hours] = th
End Sub 

, to get the total minutes,

and in the "On Exit" property run this routine:

 Private Sub total_hours_Exit(Cancel As Integer)
 
Dim th As Long
 
'th = Forms![attorney statements]![total hours]
 
th = Forms![attorney statements]![total hours]
 
Forms![attorney statements]![total hours] = th / 60
End Sub 

, to get back to hours and Minutes.

I hope what i am trying to do is clear, i can't seem to figure out how to keep the minutes under 60, and stop the division keeping the remainder.

Any help is greatly appreciated

[Scott Price]

Not sure if this will help you or not, but try this link:

http://office.microsoft.com/en-us/ac...CL100570041033

Regards,
Scott

[richbneal]

Thanks Scott - I am leaning toward data type and the final formatting of data holding up progress. I will keep trying. Thanks for the link.

[Stwange]

6.33 is six and one third hours, which is 6h20.
A simpler way to do this would be to use the existing system, and then add another step to convert it (as the decimal is also a percentage of whole, so multiply it by 60)
Eg. (I haven't test this, but if it doesn't work it should give you an idea)
Private Function convertHours(byval hour as Single) as Single
dim pos as integer
pos = instr(cstr(hour),".")
if pos <> 0 then
dim fraction as single
fraction = CSng("0" & mid$(CStr(hour),pos))
fraction = (fraction / 100) * 60
convertHours = CSng(mid$(CStr(hour),1,pos - 1)) + fraction
end if
End Function

Good Luck. And by the way, It's already past August 1st?

[richbneal]

6.33 is six and one third hours, which is 6h20.
A simpler way to do this would be to use the existing system, and then add another step to convert it (as the decimal is also a percentage of whole, so multiply it by 60)
Eg. (I haven't test this, but if it doesn't work it should give you an idea)
Private Function convertHours(byval hour as Single) as Single
dim pos as integer
pos = instr(cstr(hour),".")
if pos <> 0 then
dim fraction as single
fraction = CSng("0" & mid$(CStr(hour),pos))
fraction = (fraction / 100) * 60
convertHours = CSng(mid$(CStr(hour),1,pos - 1)) + fraction
end if
End Function

Good Luck. And by the way, It's already past August 1st?

This looks very positive, thank you very much. I guess I am more stressed than i thought. Sept 1st is what i wanted to write.

[missinglinq]

Well, duh! From looking at your code, you're apparently working with lawyers! That's enough to stress out a Buddist Monk on Valium!

Welcome to TheScripts!

Linq ;0)>

[Scott Price]

Well, duh! From looking at your code, you're apparently working with lawyers! That's enough to stress out a Buddist Monk on Valium!

Welcome to TheScripts!

Linq ;0)>

Thanks, Linq!! Made me laugh :-)

I was thinking that lawyers who like to double bill hours probably wouldn't want to implement a system more like a time-clock which would limit them from being able to double bill, but would be MUCH simpler to calculate elapsed time with since the functionality to do so is already built into Access... Didn't want to point out that earlier, though LOL

Regards,
Scott