Need to track time each day between 2 time periods then obtain sum of total hours in a week

I am trying to create a simple to track total time spent on an activity each day so that I can have a weekly total of hours spent on that activity.

I have a form with 3 textboxes as follows:

txtStarttime  (Bound to Time/Date data type field in table)
txtEndtime  (Bound to Time/Date data type field in table)
txtTotaltime (Bound to Time/Date data type field in table; I tried using a number format so that I could calculate total hours but  I received a data type mismatch error)

I need to be able to retrieve all the total time values to obtain the sum value, my problem is it only works if I format the txtTotaltime table field as a textbox which will not calculate total sum value
Note: Both, txtStarttime and txtEndtime are use the short time format. Below is the code I am using…

Private Sub txtTimeStop_LostFocus()
If [txtTimeStop] > [txtTimeStart] Then
txtTotalHours.Value = Format([txtTimeStop] - [txtTimeStart], "hh:nn")
   txtTotalHours.Value = Format([txtTimeStop] + 1 - [txtTimeStart], "hh:nn")
End If
End Sub

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
not sure what format you want to store the computed time,
see this link for reference

How to store, calculate, and compare Date/Time data in Microsoft Access
mickeyshelley1Author Commented:
Hey Cap this is a community service database, each day a start time and a finish time is entered into each record and the total hrs are currently stored in a textbox with a text format field, the problem is that I am unable to total the hours with a query because of the format of that field like I could if it were a number format. When I use a number format I get an error related to data type mismatch..
Rey Obrero (Capricorn1)Commented:

try this, store the values in Data Type Number ,Field Size Double

Private Sub txtTimeStop_LostFocus()
If [txtTimeStop] > [txtTimeStart] Then
txtTotalHours.Value = ([txtTimeStop] - [txtTimeStart]) * 24
   txtTotalHours.Value = ([txtTimeStop] + 1 - [txtTimeStart]) *24
End If
End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mickeyshelley1Author Commented:
You ALWAYS solve my problems…thank you so much AGAIN CAP
if you have your times as strings in the format say "HH:MM:AM/PM" then you could use the following to just take the starting time as a string and the end time as a string and return the hours/min between...its just an example...

This very basic function assumes you are supplying an end time that is after the start time and that the format also contains AM try it this way

Debug.Print CompareTime("9:55AM","3:25PM")

Open in new window

Public Function CompareTime(tStart As String, tEnd As String) As Long
Dim sHour As String, sMin As String, eHour As String, eMin As String
Dim realStart As Date, realEnd As Date

tStart = Left(tStart, Len(tStart) - 2)
sHour = Mid(tStart, 1, InStr(1, tStart, ":") - 1)
sMin = Mid(tStart, InStr(1, tStart, ":") + 1, 2)
realStart = TimeSerial(Val(sHour), Val(sMin), 0)

If Right(tEnd, 2) = "PM" Then
    tEnd = Left(tEnd, Len(tEnd) - 2)
    eHour = (Mid(tEnd, 1, InStr(1, tEnd, ":") - 1)) + 12
    tEnd = Left(tEnd, Len(tEnd) - 2)
    eHour = Mid(tEnd, 1, InStr(1, tEnd, ":") - 1)
End If
eMin = Mid(tEnd, InStr(1, tEnd, ":") + 1, 2)
realEnd = TimeSerial(Val(eHour), Val(eMin), 0)

CompareTime = DateDiff("n", realStart, realEnd) / 60

End Function

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.