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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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.
Gustav BrockCIOCommented:
That should be easy.

CycleTime = FinishTimeEnd - FinishTimeStart
If TimeValue(FinishTimeStart) < #10:00# And TimeValue(FinishTimeEnd) > #10:20# Then
    CycleTime = CycleTime - #00:20#
End If

Open in new window

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
Dale FyeOwner, Developing Solutions LLCCommented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

shrimpforkAuthor Commented:
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.
Gustav BrockCIOCommented:
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.
shrimpforkAuthor Commented:
@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?
Dale FyeOwner, Developing Solutions LLCCommented:
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.
shrimpforkAuthor Commented:
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.
Gustav BrockCIOCommented:
Thanks for the feedback.
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
VB Script

From novice to tech pro — start learning today.