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?
VB ScriptMicrosoft Access

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon

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 Brock

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Dale Fye

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.

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Gustav Brock

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?
Dale Fye

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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.
Gustav Brock

Thanks for the feedback.
Your help has saved me hundreds of hours of internet surfing.