We help IT Professionals succeed at work.

MS Access Time Calculations with VB

shrimpfork
shrimpfork asked
on
151 Views
Last Modified: 2018-03-07
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?
Comment
Watch Question

CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.

Author

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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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.

Author

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, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
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
    rs.movenext
Wend
dblCycleTime = dblCycleTime - intBreakLen

Open in new window

CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Thanks for the feedback.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.