Solved How to keep time in a table? (1 Viewer)

Sun_Force

Active member
Local time
Today, 22:16
Joined
Aug 29, 2020
Messages
396
I need to keep the duration of a specific event (time) in a table. This is the cycle time for a machine to manufacture a part and later will be used to calculate production ratio of the machine.

I used date/time data type for the field, but when I input 1:00:30 (1 hours, 00 minutes, 30 seconds) 1:00:30 PM.

Any kind of advice is much appreciated.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:16
Joined
May 7, 2009
Messages
19,169
add Format to your field:
hh:nn:ss
 

Sun_Force

Active member
Local time
Today, 22:16
Joined
Aug 29, 2020
Messages
396
I can work with the format of the form.
I was just wondering why the table shows it as 1:00:30 PM

I need it as a duration of time not an exact time.

thanks
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:16
Joined
May 7, 2009
Messages
19,169
duration2.png
duration3_LI.jpg
 

Attachments

  • duration1.png
    duration1.png
    10.6 KB · Views: 376
  • duration3.png
    duration3.png
    12.6 KB · Views: 393

Sun_Force

Active member
Local time
Today, 22:16
Joined
Aug 29, 2020
Messages
396
My table shows AM and PM.
I will check it on another pc to see if something's wrong with mine.

Edit : I didn't notice the format of the time in the table. Now everything is fine.

Thanks for your help.
 

isladogs

MVP / VIP
Local time
Today, 13:16
Joined
Jan 14, 2017
Messages
18,186
Do bear in mind that dates and times are stored as double datatype numbers no matter how they are formatted.
The integer part is the number of days since 30 Dec1899 and the decimal part represents the time where e.g. 06:00 = 0.25

Code:
?Now
20/09/2021 10:48:26 
?CDbl(Now)
 44459.4505092593

As a result, if you try and save the duration as a date/time field, the maximum duration you can store is 23:59:59.
If that is an issue you need to store it as number field and display it in your desired format e.g. 72 hr 15 min 25 s
 

Sun_Force

Active member
Local time
Today, 22:16
Joined
Aug 29, 2020
Messages
396
Do bear in mind that dates and times are stored as double datatype numbers no matter how they are formatted.
The integer part is the number of days since 30 Dec1899 and the decimal part represents the time where e.g. 06:00 = 0.25

Code:
?Now
20/09/2021 10:48:26 
?CDbl(Now)
 44459.4505092593

As a result, if you try and save the duration as a date/time field, the maximum duration you can store is 23:59:59.
If that is an issue you need to store it as number field and display it in your desired format e.g. 72 hr 15 min 25 s
I really appreciate and will remember this lesson.
thanks.
 

isladogs

MVP / VIP
Local time
Today, 13:16
Joined
Jan 14, 2017
Messages
18,186
You're welcome.
However I've just noticed your similar thread below which uses the correct approach.
Store the start and end times (including the date portion). Calculate the difference between them in seconds as your duration .... but do NOT store the value as a separate field.
Then display that calculated value in e.g. hr min sec format as needed
 

Sun_Force

Active member
Local time
Today, 22:16
Joined
Aug 29, 2020
Messages
396
Store the start and end times (including the date portion). Calculate the difference between them in seconds as your duration .
Yes, as you noticed it was a question concerning my other thread.
But unfortunately I can not store the start time and end time in this case. (I can but it complicates the whole thing)

This duration is used to save the duration a machine is stopped (operator's break time, part attaching, part detaching, daily maintenance, ...)
So it makes the whole case complicated if I want to add a start time and end time for each of them.
I thought it would be much easier to view the form and calculate the operation ratio if the table structure is :

StartTime - CycleTime - OfflineTime - EndTime



Thanks again.
 

isladogs

MVP / VIP
Local time
Today, 13:16
Joined
Jan 14, 2017
Messages
18,186
Standard practice is that calculated values should NOT be stored.
Whilst it may SEEM to complicate matters for now, it will definitely simplify later coding issues!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:16
Joined
Feb 19, 2002
Messages
42,970
I agree with storing start and stop time. You just have to keep in mind that the datetime data type is intended to store a point in time. It doesn't store elapsed time. So, when you are working with elapsed time, work with the lowest unit you want to display so if you want to display hh:mm:ss, then you need to calculate the difference in seconds and convert seconds to a format manually. If your accumulated value will never exceed 23:59, then you can let Access do the work for you but otherwise, you have to do it yourself. There is a useful date functions database I've posted many times. It has a procedure that helps with formatting . If you can't find it and you need it, PM me.
 

Users who are viewing this thread

Top Bottom