Link to home
Avatar of shrimpfork
shrimpforkFlag for United States of America

asked on

MS Access Time Calculations with VB

Experts, I am working within my Access DB and using VB to sum up the Cycle Times of a days worth of machine processes.  My DB records the finish time (long date) each time my CNC completes a job through the day, which I can use as the cycle time (ie. the difference between finish times of two adjacent job records).  However within my VB calculation I need to check if the cycle time included the employee's break or lunch and remove that duration from the cycle time.

For example, I have a two finish times as "12/1/2017 9:47:03 AM" and "12/1/2017 10:33:29 AM".  This calculates for 46.43 minutes, however it included a break time that occured from 10:00 am until 10:20 am which needs to be detected and removed from this particular cycle time.  Is there an easy way to check for the break time duration between a the cycle time?
Avatar of PatHartman
Flag of United States of America image

Sounds like your table is not set up correctly.  If you want to record a series of times, you need more than one record.  In this case the first should be 9:47:03 AM - 10:AM and the second should be 10:20 AM - 10:33:29

Having a break in separate columns allows for only one gap which I'm sure is incorrect.  To use it though, you would have to use start time - break start + break end to end time.
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Gustav's response depends on a static value for break times, and assumes the worker actually took the break, which may be fine, or mibht not be, depending on your desired level of accuracy.  

If you actually record worker breaks in in a table somewhere (tbl_Emp_Breaks: ID, EmpID, BreakStart, BreakEnd), then you would need to query that table for records where

([EmpID] = X) AND ([BreakStart] < [FinishTimeEnd]) AND ([BreakEnd] > [FinishTimeStart])

You could then compute the sum of the break times which occurred between the FinishTimeStart and FinishTimeEnd.
Avatar of shrimpfork


Experts, thank you for your input...
@PatHartman:  It's not a issue of the table setup.  If you revisit my description, I do have more than one record.  Each job through the CNC has a time stamp of its completion.  From the completion from one job to the completion to another job is a "cycle time".  I just need to detect and remove the break and lunch periods form that as the machine is paused during this time.

@Gustav Brock:  You made it look easy, I was working with a similar code last night and came up with this:
(The only big difference is that mine only looks if the start and finish segment cross over the start of the break period.  I'm not sure if that really matters in the end result.)
If Format(dateCycleStart, "hh:mm:ss") < dateBreakStart And dateBreakStart < Format(dateCycleEnd, "hh:mm:ss") Then
     dblCycleTime = dblCycleTime - ((dateBreakEnd - dateBreakStart) * 24 * 60)
End If

Open in new window

@Dale Fye:  Yes I have an input that I can modify the break times for a given period.  However, they rarely change.
What may cause your attempt to fail is, that you are not handling the date and time as date values.
Format returns strings, and that will mess up any comparison.
@Gustav Brock:  Yes I agree.  I'm going to rework the code to use "TimeValue".  However if I declared the date variables as a DATE, that would prevent the string comparison issue, correct?
Will you ever have a process start or finish during a break?

In your original message you indicated "two adjacent records", so your data doesn't actually look like FinishTimeStart and FinishTimeEnd, it look more like:

Process #      Action             DateTime
1                     Start                12/1/2017 9:47:03 AM
1                     End                 12/1/2017 10:33:29 AM

Is that correct?

It looks like you are trying to use VBA to compute the process time, rather than doing this in a query, is that correct?  If you create a table of break times (tbl_Breaks) with fields BreakStart and BreakEnd, both date fields with values:

BreakStartTime    BreakEndTime    Duration
10:00 AM               10:20 AM                    20
 2:30 PM                2:40 PM                      10

Note, in Access, you will not be able to have BreakStart and BreakEnd values that are just times, they will have to be a date/time value, so you will need to include a date in that value, but since you are using the TimeValue( ) function in the SQL below, the date will not be pertinent.

Then you might be able to do something like:
strSQL = "Select Duration FROM tbl_Breaks " _
       & "WHERE TimeValue(BreakStartTime) < TimeValue(#" & FinishEndTime & "#) " _
       & "AND TimeValue(BreakEndTime) > (TimeValue(#" & FinishStartTime & "#)"
Set rs = currentdb.openrecordset(strsq)
Dim intBreakLen as integer
While not rs.eof
    intBreakLen = intBreakLen + rs!Duration
dblCycleTime = dblCycleTime - intBreakLen

Open in new window

The problem is the table - you might want to post it since we are still guessing.

What you are describing is one record with the over all start time and end time and 0-m break records.  This complicates the calculation since you need to use the first record to calculate the over all time and then subtract the time from the break records.

Whereas, if the table worked as I suggested with multiple start and end times and no "gap" records, you simply calculate the elapsed time for each record and sum it with all the others in the set.

It is a matter of perspective.  If the gaps are important and you want to track them then the first method would make sense but if all you really care about is the actual time it took to do something then the second method is simpler.
Thank you experts.  Gustav Brock nailed it.  I am working with the existing data that have been collecting for 10 years, which is a completion time stamp for each CNC cycle.  There is no problem of the tables, just looking to use VB with the information that I have.  As mentioned the machine is paused during lunch and break and a job will not finish during the break or lunch time slots.  I think we got what we were looking for.  Again, thank you for the time and efforts.  I love this site.
Thanks for the feedback.