Cumulative Hours and Minutes 1

[wvmbark]

I need to create a field to capture time duration in hours and minutes(aircraft flight time per month). Ie., 1000:55 for 1000 hours and 55 minutes.

What is the best way to format this field to allow calculations that provide cumulative time duration over a given period, or cumulative time to date considering the latest duration entry?

Thanks!!

Mike Barkley

 

[JeremyNYC]

Mike,

You don't want to use a time/date format, that's for sure. Those fields capture a specific moment, not a duration. You have lots of options. The easiest one is to store a large integer of the smallest unit you'll be measuring, which in your case looks like minutes. Then you can just convert that to whatever format you want for display and printing purposes.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[wvmbark]

Thanks, but I do not want to perform manual calculations to convert the 1000:55 to a new format each time before entering. I would like to enter "1000:55" and have Access recognize this as 1000 hours and 55 minutes, and be able to calculate on them with as few functions as possible.

Mike

 

[SkipVought]

Then

    x = "1000:55"
    dur = (Left(x, InStr(x, ":") - 1) + Right(x, 2) / 60) / 24

duration is in days. You can then use with dates.

If you do not divide by 24 duration is in hours.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[CajunCenturion]

There are two issues here, one is the display format of the data, and one is the storage format.

With respect to the storage format, I agree with JeremyNYC, that the values should be stored in the smallest unit resolution, which in this case, appears to be minues. It's far simpler to add two minutes fields (135 + 235) than it is to add two composite fields (2:15 + 3:55).
I also understand you desire to use a display format in Hours:Minutes for user ease. That can be accomplished with two functions, one on the data entry side to convert hhh:mm to minutes ((hours * 60) + minutes), and on the display, to convert minutes to hours:minutes (hours = int(minutes/60) ; min = minutes - (hours * 60))

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[wvmbark]

CajunCenturion,

Thanks for your input. Could you please expand your thoughts briefly to explain how you'd format the table field, how you'd format the data entry field in the data entry form, and how you'd get the calculated number of minutes to the table? It would be nice if the user could enter the numbers 1000 and 55 and then the proposed equivalent be stored in the database, which I believe is what you are suggesting. I'm just not exactly sure how to go about it.

Thanks for your help.

Mike

 

[CajunCenturion]

I would do something along the lines of the following:

First, I would create a Long Integer database field to store the total flight time in minutes. I would create a textbox (txtHiddenTime) on the form, bound to that column, but set the Visible property to false.

Second, I would create a visible textbox (txtVisibleTime) on the form to show the formatted time for the users.

Third, I would add the following code to perform the proper translations to the visible time. I'm using Form_Load, but depending on the behavior you may also need this same block of code in the Form_Activate and/or Form_Current events as well. That will depend on the behavior of this form, and interaction with other forms.

Private Sub Form_Load()
   
   Dim lLng_Minutes     As Long
   Dim lLng_Seconds     As Long
   
   lLng_Minutes = Int(txtHiddenTime / 60)
   lLng_Seconds = txtHiddenTime - (lLng_Minutes * 60)
   txtVisibleTime = Trim(lLng_Minutes) & ":" & Trim(lLng_Seconds)

End Sub

I would also add the following event handler on the txtVisibleTime textbox to re-calculate the proper minutes based on the user input.

Private Sub txtVisibleTime_LostFocus()

   Dim lInt_Delim As Integer
   Dim lLng_Hours As Long
   Dim lLng_Minutes As Long
   
   lInt_Delim = InStr(txtVisibleTime, ":")
   If (lInt_Delim > 1) Then
      lLng_Hours = Val(Left(txtVisibleTime, (lInt_Delim - 1)))
      lLng_Minutes = Val(Mid(txtVisibleTime, (lInt_Delim + 1)))
   Else
      lLng_Hours = 0
      lLng_Minutes = IIf((lInt_Delim = 0), Val(txtVisibleTime), Val(Mid(txtVisibleTime, 2)))
   End If
   txtHiddenTime = (lLng_Hours * 60) + lLng_Minutes

End Sub

I hope this at least get you going down a workable path.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[wvmbark]

Thanks for your help! I'll respond here if I have any problems, though it could be a few days before I get to try out your suggestions.

Thanks again!

Mike