By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,505 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

vba to split duration in each hour and add record

P: 171
I have an access table (MasterLogs)with Date, UserName, Position, loginTime, logoutTime.(Login and logout field type is date&time). I am trying to read each record in this table and add records to another table (PositionHrs). The field in that table are:

Column 4 on wards are basically to fill time duration.

An example of Masterlogs table record is, Date:01-Aug-17, Username:xyz, Position:caller1, login: 01-Aug-17 00:05 and logout: 01-Aug-17 02:45

I am looking for a VBA code which can loop through the records in masterlogs and for each record if there are any minutes in any of the 24 hour period the user worked, then duration to be inserted to corresponding time duration column in the positionHrs table. An example for the PositionHrs table after the code processed shall be Date:01-Aug-17, Username:xyz, Position:caller1, 0000-0059:00:54 0100-0159:01:00, 0200-0259:00:45. All other time duration columns will have 00:00. Hope I have explained well.

Can anyone please help me to fix this ? I have a vba code which is only giving the first duration and goes to next record
Expand|Select|Wrap|Line Numbers
  1. Sub HourlyDuration()
  2. Dim rs1 As Recordset 
  3. Dim rs2 As Recordset 
  4. Set rs1 = CurrentDb.OpenRecordset("MasterLogs") 
  5. Set rs2 = CurrentDb.OpenRecordset("PositionHrs")
  7. Do While Not rs1.EOF
  8. 'below for adding minutes in rs2![0000-0059] "hour1"
  10. If Format(rs1![On], "hh:nn") >= #12:00:00 AM# And Format(rs1![On], "hh:nn") <= #12:59:59 AM# Then
  12.         rs2.AddNew
  13.         rs2![Date] = rs1![Date]
  14.         rs2![0000-0059] = Format(TimeValue("01:00:00") - TimeValue(RoundTime(rs1!On, 60)), "hh:nn")
  16. ''roundtime is a function to round time to the next minute 
  18.         rs2!Position = rs1!Position
  19.        rs2!Staff = rs1!Staff
  20.         rs2.Update
  21.     End If
  23. 'for next duration column rs2![0100-0159] "hour2"
  25. If Format(rs1![On], "hh:nn") >= #1:00:00 AM# And Format(rs1![On], "hh:nn") <= #1:59:59 AM# Then
  27.        rs2.AddNew
  28.         rs2![Date] = rs1![Date]
  29.         rs2![0100-0159] = Format(TimeValue("02:00:00") - TimeValue(RoundTime(rs1!On, 60)), "hh:nn")
  30.         rs2!Position = rs1!Position
  31.         'rs2!Staff = rs1!Staff
  32.         rs2.Update
  33.     End If
  34. ' same conditions repeated for all hours, and then
  36. rs1.MoveNext
  38. Loop
  41. MsgBox ("Finished")
  44. rs1.Close
  45. Set rs1 = Nothing
  46. rs2.Close 
  47. Set rs2 = Nothing 
  48. End sub
I know this is totally wrong. Someone told me that I have to split the 24 hours into MOD and then add to the duration columns which I am unable to do. Please help
Oct 18 '17 #1

✓ answered by ADezii

Changed the overall Logic with no Code Duplication and added a Log Date Field which would indicate spanning a Day. I feel as though we are very close now.

P.S. - See entries for Babe Ruth and Barney Rubble.

Share this Question
Share on Google+
29 Replies

Expert 100+
P: 634

Not sure if this is a great help to you but but it sounded interesting, so the following is one method for abtaining the minutes in a given perion
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Dim StartTime As Date
  3. Dim EndTime As Date
  5. Sub AssignTimeToPeriods()
  6.     Dim i As Integer
  7.     Dim ThisPeriodStart As Date
  8.     Dim ThisPeriodEnd As Date
  9.     Dim ThisPeriodTime As Single
  10.     Dim ThisPeriodFieldName As String
  13.     StartTime = #8/1/2017# + 5 / 24 / 60
  14.     EndTime = #8/1/2017# + 2 / 24 + 45 / 24 / 60
  17.     For i = 0 To 23
  19.         ThisPeriodStart = Int(StartTime) + i / 24
  20.         ThisPeriodEnd = Int(StartTime) + (i + 1) / 24
  23.         ThisPeriodFieldName = Format(ThisPeriodStart - Int(ThisPeriodStart), "hhmm") & "-" & Format(ThisPeriodEnd - Int(ThisPeriodEnd) - 1 / 24 / 60, "hhmm")
  26.         If StartTime <= ThisPeriodEnd And EndTime > ThisPeriodStart Then
  29.             If StartTime > ThisPeriodStart Then
  30.                 If EndTime < ThisPeriod Then
  31.                     'START AND END BOTH WITHIN THIS PERIOD
  32.                     ThisPeriodTime = EndTime - StartTime
  33.                 Else
  35.                     ThisPeriodTime = ThisPeriodEnd - StartTime
  36.                 End If
  37.             ElseIf EndTime >= ThisPeriodEnd Then
  39.                 ThisPeriodTime = ThisPeriodEnd - ThisPeriodStart
  40.             Else
  42.                 ThisPeriodTime = EndTime - ThisPeriodStart
  43.             End If
  45.             'Change 'PERIOD' FROM DAYS TO MINUTES (OR AS REQUIRED)
  46.             ThisPeriodTime = ThisPeriodTime * 24 * 60
  49.             Debug.Print ThisPeriodFieldName & " = " & ThisPeriodTime
  50.         End If
  52.     Next i
  53. End Sub
I have used the time values and periods indicated in the post.

Does that help?

There could be a simpler way that I don't know!!

Oct 20 '17 #2

Expert 5K+
P: 8,597
  1. For some reason, I found this to be a rather difficult challenge, but I do feel as though I have arrived at a viable solution that produces the following results based on your initial Post. The results that I arrived at are as follows (expand the Window):
    Expand|Select|Wrap|Line Numbers
    1. Date          User Name     Position      Start         Finish        Duration
    2. --------------------------------------------------------------------------------
    3. 8/1/2017      xyz           Caller1       00:00         00:59         00:55
    4. 8/1/2017      xyz           Caller1       01:00         01:59         00:60
    5. 8/1/2017      xyz           Caller1       02:00         02:59         00:46
    6. 8/1/2017      xyz           Caller1       03:00         03:59         00:00
    7. 8/1/2017      xyz           Caller1       04:00         04:59         00:00
    8. 8/1/2017      xyz           Caller1       05:00         05:59         00:00
    9. 8/1/2017      xyz           Caller1       06:00         06:59         00:00
    10. 8/1/2017      xyz           Caller1       07:00         07:59         00:00
    11. 8/1/2017      xyz           Caller1       08:00         08:59         00:00
    12. 8/1/2017      xyz           Caller1       09:00         09:05         00:00
    13. 8/1/2017      xyz           Caller1       10:00         10:59         00:00
    14. 8/1/2017      xyz           Caller1       11:00         11:59         00:00
    15. 8/1/2017      xyz           Caller1       12:00         12:59         00:00
    16. 8/1/2017      xyz           Caller1       13:00         13:59         00:00
    17. 8/1/2017      xyz           Caller1       14:00         14:59         00:00
    18. 8/1/2017      xyz           Caller1       15:00         15:59         00:00
    19. 8/1/2017      xyz           Caller1       16:00         16:59         00:00
    20. 8/1/2017      xyz           Caller1       17:00         17:59         00:00
    21. 8/1/2017      xyz           Caller1       18:00         18:59         00:00
    22. 8/1/2017      xyz           Caller1       19:00         19:59         00:00
    23. 8/1/2017      xyz           Caller1       20:00         20:59         00:00
    24. 8/1/2017      xyz           Caller1       21:00         21:59         00:00
    25. 8/1/2017      xyz           Caller1       22:00         22:59         00:00
    26. 8/1/2017      xyz           Caller1       23:00         23:59         00:00
    27. --------------------------------------------------------------------------------
    28. --------------------------------------------------------------------------------
  2. Rather then go into a long-winded explanation of the Logic, I'll simply Upload the Demo and let you decide if it is worthwhile. I am sure that there has to be a better solution, but it simply eludes me at this time.
Attached Files
File Type: zip Time (27.0 KB, 29 views)
Oct 20 '17 #3

Expert Mod 10K+
P: 12,315
You can do this with a query if you want to forgo the code.

You can either:

use 24 iif() calls which could be unwieldy and slow if you have a lot of records but if it's only one at a time it's fine


use a bridge table that has 24 records, 1 for each hour, join that to on the time range, and then pivot the result
Oct 20 '17 #4

Expert 5K+
P: 8,597
use a bridge table that has 24 records, 1 for each hour, join that to on the time range, and then pivot the result
Out of curiosity, Rabbit, how would you Join these Tables, and on what Fields? For my Demo, I used a Reference Table depicted below. Would your Bridge Table be the same or similar. Thanks in advance for your explanation.
Expand|Select|Wrap|Line Numbers
  1. ID    Start    Finish
  2. 1      0:00     0:59
  3. 2      1:00     1:59
  4. 3      2:00     2:59
  5. 4      3:00     3:59
  6. 5      4:00     4:59
  7. 6      5:00     5:59
  8. 7      6:00     6:59
  9. 8      7:00     7:59
  10. 9      8:00     8:59
  11. 10     9:00     9:05
  12. 11    10:00    10:59
  13. 12    11:00    11:59
  14. 13    12:00    12:59
  15. 14    13:00    13:59
  16. 15    14:00    14:59
  17. 16    15:00    15:59
  18. 17    16:00    16:59
  19. 18    17:00    17:59
  20. 19    18:00    18:59
  21. 20    19:00    19:59
  22. 21    20:00    20:59
  23. 22    21:00    21:59
  24. 23    22:00    22:59
  25. 24    23:00    23:59
P.S. - The [Start] and [Finish] Fields are Date/Time.
Oct 20 '17 #5

P: 171
Dear ADezii
Thank you for the demo and a positive approach. This looks like I am getting very close to the solution what I am looking for.
I am trying with the demo db but on this line "Debug.Print String(80, "-")" i was getting a vb error and when checked in vba references it shows missing outlook 14.0 and excel 14.0 object missing.
I have selected those objects in vba reference and on the form button click it prints 24 lines for each user.How can I print only the rows with if there is a value in duration (I dont want to see the time periods when there is no duration).
And, instead of printing (debug.print) I would like to store this in another table. Please guide me
Oct 21 '17 #6

Expert 5K+
P: 8,597
One Error is resulting from unresolved References in the Demo, the Debug.Print Statement can be rewritten (not sure why it is throwing an Error), and showing only positive Durations will be an easy fix. Storing the Results in another Table will be a little difficult but can also be done. I will work on it today and Upload a Revision when complete.
Oct 21 '17 #7

P: 171
Dear ADezii
Thank you very much for your kindness and support. This is a great help I appreciate much. Both errors are resolved after I selected the references for Outlook and Excel. I will wait for your kind update
Oct 21 '17 #8

Expert 5K+
P: 8,597
Here is the Revised Code along with the Revised Demo. I never meant my solution to be this complex, but it sort of developed a life of it's own and grew. I would patiently wait and see if Rabbit can demonstrate a more efficient and simpler SQL-based approach. He is infinitely more qualified in this area than I am. I also added 2 additional Records in the MasterLogs Table in different Time Slots to verify that the Logic is indeed sound. In any event, download the Revised Demo, and let me know what you think.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdTest_Click()
  2. On Error GoTo Err_cmdTest_Click
  3. Dim MyDB As DAO.Database
  4. Dim rst1 As DAO.Recordset       'MasterLogs
  5. Dim rst2 As DAO.Recordset       'tblTimeSlots
  6. Dim rst3 As DAO.Recordset       'PositionHrs
  7. Dim intCtr As Integer
  8. Dim intMinBld As Integer        'Keep Running Count for Time Slots
  10. 'Clear the PositionHrs Table
  11. CurrentDb.Execute "DELETE * FROM PositionHrs", dbFailOnError
  13. Set MyDB = CurrentDb
  14. Set rst1 = MyDB.OpenRecordset("MasterLogs", dbOpenForwardOnly)
  15. Set rst2 = MyDB.OpenRecordset("SELECT * FROM tblTimeSlots ORDER BY ID", dbOpenSnapshot)
  16. Set rst3 = MyDB.OpenRecordset("PositionHrs", dbOpenDynaset)
  18. With rst1
  19.   Do While Not .EOF
  20.     Do While Not rst2.EOF
  21.       For intCtr = 0 To DateDiff("n", TimeValue(![LogIn]), TimeValue(![LogOut]))
  22.         If TimeValue(DateAdd("n", intCtr, ![LogIn])) >= rst2![Start] And _
  23.            TimeValue(DateAdd("n", intCtr, ![LogIn])) <= rst2![Finish] Then
  24.           intMinBld = intMinBld + 1
  25.         End If
  26.       Next
  27.         If intMinBld > 0 Then
  28.           rst3.AddNew
  29.             rst3![Date] = ![Date]
  30.             rst3![User Name] = ![UserName]
  31.             rst3![Position] = ![Position]
  32.             rst3![Start] = Format(rst2![Start], "hh:nn")
  33.             rst3![Finish] = Format(rst2![Finish], "hh:nn")
  34.             rst3![Duration] = Format$(intMinBld, "00:00")
  35.           rst3.Update
  36.         End If
  37.           intMinBld = 0
  38.           rst2.MoveNext
  39.     Loop
  40.       .MoveNext
  41.       rst2.MoveFirst
  42.   Loop
  43. End With
  45. rst1.Close
  46. rst2.Close
  47. rst3.Close
  48. Set rst1 = Nothing
  49. Set rst1 = Nothing
  50. Set rst3 = Nothing
  52. With DoCmd
  53.   .OpenTable "PositionHrs", acViewNormal, acReadOnly
  54.   .Maximize
  55. End With
  57. Exit_cmdTest_Click:
  58.   Exit Sub
  60. Err_cmdTest_Click:
  61.   MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
  62.     Resume Exit_cmdTest_Click
  63. End Sub
P.S. - I am pretty sure that the Code Logic can be improved upon. Addressing every minute within the LogIn/LogOut Range to see what Time Slot it falls into is time consuming and not very efficient. The problem is that I simply do not have the time to spend on the improvements.
Attached Files
File Type: zip Time (28.5 KB, 17 views)
Oct 21 '17 #9

P: 171
Dear ADezii
Thank you for the time you spent to support me. It is a great help.
I was trying with few sample entries, and noticed that when the logout extends to next day the code is not processing that record. Example, if login 01-Aug-17 22:58 and logout 02-Aug-17 00:35, there is no records written to the PositionHrs table. In reality there will be entries for each date with login same date but logout after midnight.
Hope you can adjust the code to include those entries as well.
Thank you for the support and patience you have shown towards resolving this
Oct 21 '17 #10

Expert 5K+
P: 8,597
and noticed that when the logout extends to next day the code is not processing that record
You are correct, the Logic does not allow for days being spanned (wasn't aware that this was a requirement). When I get a chance, I'll see what I can do to compensate for this. Got a couple of minutes, so try Revision 2, specifically looking at Caller17 (Babe Ruth) who has LogIn and LogOut span 2 days (9/1/2017 and 9/2/2017).
Attached Files
File Type: zip Time (27.2 KB, 33 views)
Oct 21 '17 #11

Expert Mod 10K+
P: 12,315
@Adezii, you would join on overlapping time between the two tables. To do so, one side would need to be converted to the same date as the other.

But given the new requirement that the timestamps can span multiple days, my original suggestion would no longer work.
Oct 21 '17 #12

Expert 5K+
P: 8,597
Thanks Rabbit. I am under the assumption, that under the given circumstances, there is no purely SQL-based solution. Do you consider that an accurate statement?
Oct 21 '17 #13

Expert Mod 10K+
P: 12,315
There is, but it would be even more convoluted. It would require a dummy table of a bunch of rows representing hour ranges from midnight spanning multiple days. It wouldn't be foolproof because you don't know how many rows you would need to cover the range of days between the start and end time in the target table. It could cause an unwieldy join between the two tables.
Oct 22 '17 #14

P: 171
Dear ADezii
Thank you for the willingness and patience to resolve my issue. I have tried the second demo also. The Date field in the "PositionHrs" table can be from the date part of login entry. I have a humble suggestion for a different approach which I feel may workout, but my little knowledge cannot put it in a code. It is like, First, read each record from MasterLogs table, then add a record in the "MasterLogs" table if the logout is in next date. and the logout of the record which has a logout nextdate can be modified as 23:59:59. The new record added in the "MasterLogs" table will have nextdate(date from the logout) as date, user same as user, position same as position, login will be with nextdate and 00:00:00 and logout will be same as the base record. Then we don't need to worry about the complications of getting entries to the "PositionHrs" table for logout next date. In my records a logout will stretch to next date maximum not beyond that. Once all the records in the "masterLogs" where logout is nextdate are split and added to the masterlogs table, your code from first demo can start the other process and add records to the "PositionHrs" table. Hope this is not a foolish approach
Oct 22 '17 #15

Expert 5K+
P: 8,597
We arrive at what I feel is a workable solution, or something relatively close, and you now wish to change your strategy. I really do not understand this logic.
Oct 23 '17 #16

P: 171
Dear ADezii
Sorry, you misunderstood. I thought the demo revised 2 DB still incomplete and you are trying again with some options. That is why I suggested an alternate approach. If the revised2 is workable, I can start using it. Please let me know
Oct 23 '17 #17

Expert 5K+
P: 8,597
If the revised2 is workable, I can start using it.
Only you will know if it is 'workable' or not.
Oct 23 '17 #18

Expert Mod 10K+
P: 12,315
Attached is an example of how this would be done without VBA code. It only accounts for time ranges spanning 2 days. You would have to add more records to the time slots table to account for more days.
Attached Files
File Type: zip (35.3 KB, 19 views)
Oct 23 '17 #19

P: 171
Dear ADezii
Sorry for the delayed reply. I was busy with some personal travelling. The solution is workable but only one issue I am having. When it capture "Start" and Finish" for the logout in next date, that particular entry "00:00" 00:59 or any time in tha next date, the Date should show the logout date. Because "Start" and "Finish" time is in the next date. Hope my explanation is clear. Please help me to resolve that.
Oct 27 '17 #20

Expert 5K+
P: 8,597
I am a little hazy on this aspect. Please provide some Sample Date showing what the Output should look like for day spans. Hopefully a visual display will clarify matters for me.
Oct 28 '17 #21

P: 171
Dear ADezii
Thank you for the kind reply. I am attaching the same DB you have provided, but with manually adjusted expected results in the"PositionHrs" table. As you can see from the "MasterLogs" table, record 4 spans to 2 dates and in the expected results "PositionHrs" table the expected results are in record number 12 to 15. Hope that will give you a clear picture about what I am looking for. The login and logout fields doesnt need to be written to the "PositionHrs" table.
Attached Files
File Type: zip Time (21.8 KB, 15 views)
Oct 28 '17 #22

Expert 5K+
P: 8,597
I will have another look when I get a chance.
Oct 28 '17 #23

Expert 5K+
P: 8,597
Right now, I absolutely see no easy method to accomplish what you have requested since it would involve 2 different Methods of displaying the Data, one for Time Slots in a single Day, and another for spanning Multiple Days. What I have done is to essentially run the same block of Code twice, catching first the single Day Time Slots and then Multiple Day Time Slots. This will display the Records in PositionHrs first by Single Day, then by Multiple Day and will not reflect the natural order of Records in MasterLogs. This is not really desirable and neither is the duplication of Code, but it is all that I can come up with at this point in time. As soon as I get more time to look at it, I'll give it another try. In any event, take a look at the Revised Demo and see what you think.
Attached Files
File Type: zip Time (23.3 KB, 25 views)
Oct 28 '17 #24

P: 171
Dear ADezii
Thank you for the effort and patience you have shown. Instead of repeating the code block, is there any way we can while adding the record which has next day logout to have the date captured from the logout. But not for the time period 2300-2359 - That should have date as login date.
Other wise the date will show previous date for hours in next date.
Oct 30 '17 #25

Expert 5K+
P: 8,597
Display only the Logout and not Login Date in PositionHrs?
Oct 30 '17 #26

P: 171
Dear ADezii
The date is based on login and logout. for each hour in a login date the duration need to be found. and if the login and logout is different dates, then upto time 2359 the duration will fall into login date. any duration after 2359 of the login date will be recorded for the logout date. an example is if login 01-Aug-17 2350 and logout 02-Aug-17 0015, then there will be 2 entries need to be added to PositionHrs. 1 entry for 01-Aug-17 2300-2359 with a duration of 10 minutes and 2nd entry for 02-Aug-17 for time period 0000-0059 with a duration of 15 minutes. Hope my explanation is not confusing you. Please help
Oct 30 '17 #27

Expert 5K+
P: 8,597
Changed the overall Logic with no Code Duplication and added a Log Date Field which would indicate spanning a Day. I feel as though we are very close now.

P.S. - See entries for Babe Ruth and Barney Rubble.
Attached Files
File Type: zip Time (29.0 KB, 22 views)
Oct 30 '17 #28

P: 171
Dear ADezii
Awesome. Even you mentioned that we are very close, but I feel that we have done. That is my first impression after a quick look. I will run with more records and let you know the out come. Thank you so much
Oct 31 '17 #29

Expert 5K+
P: 8,597
You are quite welcome, Good Luck with your Project!
Oct 31 '17 #30

Post your reply

Sign in to post your reply or Sign up for a free account.