Create a data entry form for time duration (hhh:mm:ss)

[lone_rider15]

Hello Everyone,

I have an Access Application front end with SQL 2014 Enterprise as back end. I would like to add some tables and forms for my teams daily statement.
There are 8 teams. Team sizes ranging from 2 to 64. So their daily target hours are different.

Table structure will be same for every team.
I can't determine the data type for Login Hours :bangheadtime duration) for example 500:30:15 (hhh:mm:ss). Users will use a form for data entry.

I will have to summarize the hours daily and monthly.

Hope you guys can point me to the right direction. I have attached an excel file for sample data with summary and my sample database with one table and form.

Thanks in advance.

 

[Ranman256]

table:
personId, StartTime , EndTime
1, 12/21/2016 9:00 AM , 12/21/2016 5:00 pm

use a query to calc elapsed time, DateDiff("h",[startTime],[EndTime]) as ElapsedHrs
1, 12/21/2016 9:00 AM , 12/21/2016 5:00 pM , 8

 

[lone_rider15]

table:
personId, StartTime , EndTime
1, 12/21/2016 9:00 AM , 12/21/2016 5:00 pm

use a query to calc elapsed time, DateDiff("h",[startTime],[EndTime]) as ElapsedHrs
1, 12/21/2016 9:00 AM , 12/21/2016 5:00 pM , 8

Thanks for the reply.
Sorry, can't do that. There is no StartTime or EndTime to calculate elapsed time. I have just hours (for example 500:00:00) worked in a specific date (Dec 20, 2016). I need to input this in a table with a form.

 

[Ranman256]

if time is entered as 500:00:00, (not good)
then you must break it up separately into Hrs, min, secs
then sum them all HrsTot, MinTot, SecTot
convert seconds to hrs
convert minutes to hrs
sum ALL hrs

youre going to need a lot of queries.

 

[Minty]

As Ranman has said - whenever times are involved, you are much better off using start and end times, as it accommodates the time element correctly.

If you aren't worried about the seconds, I personally would simply record a total minutes figure as a long integer - at least that way you will have relatively simple sums and totals that you can then display as hours and minutes at the final stage of your reporting.

Currently your data if it is stored as you have described, it will be a nightmare to work with.

 

[lone_rider15]

As Ranman has said - whenever times are involved, you are much better off using start and end times, as it accommodates the time element correctly.

If you aren't worried about the seconds, I personally would simply record a total minutes figure as a long integer - at least that way you will have relatively simple sums and totals that you can then display as hours and minutes at the final stage of your reporting.

Currently your data if it is stored as you have described, it will be a nightmare to work with.

Thanks for taking a look into my problem.
The time duration is not calculated in this database. We get this report from another source. If the time duration gets stored in this database as long integer it will not be a problem. I only need a form control (text box) to input the data. The users will input as hhh;mm:ss in the form control and it will be stored in the table as long integer. Is there any way to do that?

 

[lone_rider15]

if time is entered as 500:00:00, (not good)
then you must break it up separately into Hrs, min, secs
then sum them all HrsTot, MinTot, SecTot
convert seconds to hrs
convert minutes to hrs
sum ALL hrs

youre going to need a lot of queries.

Thanks.
The time duration is not calculated in this database. We get this report from another source. If the time duration gets stored in this database as long integer it will not be a problem. I only need a form control (text box) to input the data. The users will input as hhh;mm:ss in the form control and it will be stored in the table as long integer. Is there any way to do that?

 

[Minty]

Yes - I would probably create a function to take the value string as it is, so you can cut and paste it from your existing data, then have a cmd button to convert it and store it along with any other required data.
Split out the hours, Multiply them by 60, add the remaining minutes and there is your total.
Have a look at the Instr() or Split() function to split the string into the relevant parts

 

[lone_rider15]

Thank you guys. I was able to create the data entry form as I needed and used SPLIT function for converting as suggested. I was able to prepare a report to show the time as HH:MM:SS.

Now I need to create another form for updating the data. I need to show the users the seconds in HH:MM:SS format and they will change if needed back to seconds.
Hope you can point me to the right direction again. I have updated the attachment with my progress.

Thanks in advance.

 

[Minty]

I think I would use a pop up form, pass in the field name, record ID and the value you are updating using the OpenArgs property. Open it on double click of the report form controls you already have.
This way one pop up form can update any of the individual values.
Present the value on the pop up form in the HHH:MM:SS fashion , and allow them to enter it like that as well. Use your existing function to recalculate and update the stored value.

 

[lone_rider15]

Thanks all for your help. I was able to develop what I wanted.
For future reference:
I created a form with unbound text boxes for time duration format (HH:MM:SS) and added a command button to convert the HH:MM:SS to seconds which will fill up the bound text boxes linked to the table.
Function to convert HH:MM:SS to seconds:

Public Function HMStoSec(strHMS As String) As Long
        HMStoSec = Split(strHMS, ":")(0) * 3600 + _
                   Split(strHMS, ":")(1) * 60 + _
                   Split(strHMS, ":")(2)
    End Function

Then added another form with the same control boxes to update the data. In this box I added couple of control boxes to filter the data I want to update. A command button will fill up the data in the form and convert seconds to HH:MM:SS format for the user view. The user will make necessary changes and update the data.
Function to convert seconds to HH:MM:SS:

    Public Function SecToHMS(intSec As Long) As String
        SecToHMS = Format([intSec] \ 3600, "00") & ":" _
                    & Format(([intSec] - ([intSec] \ 3600) * 3600) \ 60, "00") & ":" _
                    & Format(([intSec] Mod 3600) - (([intSec] - ([intSec] \ 3600) * 3600) \ 60) * 60, "00")
    End Function

Last, I created couple of reports to view the data for daily view, total and summary. I used the below code to view time duration in HH:MM:SS format.

Format(Int([FieldName]/3600),"00") & ":" & Format(Int(([FieldName]-(Int([FieldName]/3600)*3600))/60),"00") & ":" & Format((([FieldName] Mod 60)),"00")

Thanks again.

 

[lone_rider15]

I think I would use a pop up form, pass in the field name, record ID and the value you are updating using the OpenArgs property. Open it on double click of the report form controls you already have.
This way one pop up form can update any of the individual values.
Present the value on the pop up form in the HHH:MM:SS fashion , and allow them to enter it like that as well. Use your existing function to recalculate and update the stored value.

Thank you very much. Your direction was a great help.