Solved Entering Length Of Time as hh:mm:ss and saving as seconds (1 Viewer)

[Bloodlvst]

So, I have a form where I am saving an average length of time, which we enter manually from another source.

For ease of use for the people using the form, the length of time needs to be entered in a format like hh:mm:ss on the form, and then converted to seconds and saved as a long integer (I'm assuming this is how I would need to save it). So for example, the user enters 01:30:04 into the field. The value stored in the table would need to be 5404. Then, if the user views the record or needs to edit it, it will be displayed as 01:30:04.

I can get a field to display an integer in the hh:mm:ss format, but I'm at a loss as to how I would actually display it based on the field's 5404 value, for example, and actually save it to the table in the proper format.

Any ideas?

 

[theDBguy]

Hi. I think you have an interesting dilemma. I always have issues with dates and times too. I know there are available functions to reformat a date/time value to almost anything you like, but I am wondering if in your case it might require two fields (although, that's not normal). Or, you could go with one field and simply use a Text field. You can then use a function to convert it into seconds in a query, so you can use it on your form or reports.

 

[ebs17]

A conversion is easy, in both directions:

?DateDiff("s", #0:00:00#, #01:30:04#)
 5404
?DateAdd("s", 5404, #0:00:00#)
01:30:04

Does that help?

Eberhard

 

[Bloodlvst]

A conversion is easy, in both directions:

?DateDiff("s", #0:00:00#, #01:30:04#)
5404
?DateAdd("s", 5404, #0:00:00#)
01:30:04

Does that help?

Eberhard

I can give it a go for sure! Where exactly in my form or field properties would I put these statements?

 

[ebs17]

One time is double internally. The correct value can be seen, for example, if you multiply by 1:

?#01:30:04# * 1
6,25462962962963E-02

The integer (long) is something very different. You can convert the two into each other, but you cannot edit the one as the other via the format property.

You can use a query for a view only

SELECT DateAdd("s", YT.Longfield, #0:00:00#) AS MyTime
FROM YourTable AS YT

However, you cannot edit a calculated value in a recordsource. Here you probably need another unbound field in the form so that the contents can be handed over to each other.

Now the question would be how the form should really be structured. I think I would use a pop-up form for editing only one record at a time with only unbound fields.

Eberhard

 

[MajP]

I always find it more flexible to enter elapsed times with three unbound fields and a single bound field for totalseconds. On the after update of the txtHH, txtMM, txtSS for each
me.totalSeconds = nz(txtHH/3600,0) + nz(txtMM/60,0) + nz(txtss). This gives the user flexibility. If they want to enter just seconds 5405, or 79 minutes and 22s, or 45 hours, 50 minutes, 60 seconds.
Only drawback it is so flexible that you cannot really error check what they really meant. BUt if you divide totalseconds/seconds in a day that then becomes a real time.

 

[CJ_London]

For ease of use for the people using the form, the length of time needs to be entered in a format like hh:mm:ss on the form, and then converted to seconds and saved as a long integer

why do that? dates and times are stored as decimal numbers (to 10dp if I recall correctly)
Providing your maximum length of time is less than 24 hours, it then just becomes a question of formatting

?cdbl(#22:15:12#)
0.927222222222222
?format(0.927222222222222,"hh:mm:ss")
22:15:12

how I would actually display it based on the field's 5404 value

the decimal part is the number of seconds divided by the number of seconds in a day i.e. 86400 so you could simply divide your seconds by 86400.

?format(5404/86400,"hh:mm:ss")
01:30:04

I repeat this only works if the number of hours are less than 24 - if more, then formatting won't work

115404 seconds is greater than a day - over 32 hours
?115404/86400
1.33569444444444

?format(115404/86400,"HH:mm:ss")
08:03:24

you need to add 24 to the 08 - but you cannot use formatting

 

[Bloodlvst]

why do that? dates and times are stored as decimal numbers (to 10dp if I recall correctly)
Providing your maximum length of time is less than 24 hours, it then just becomes a question of formatting

?cdbl(#22:15:12#)
0.927222222222222
?format(0.927222222222222,"hh:mm:ss")
22:15:12

the decimal part is the number of seconds divided by the number of seconds in a day i.e. 86400 so you could simply divide your seconds by 86400.

?format(5404/86400,"hh:mm:ss")
01:30:04

I repeat this only works if the number of hours are less than 24 - if more, then formatting won't work

115404 seconds is greater than a day - over 32 hours
?115404/86400
1.33569444444444

?format(115404/86400,"HH:mm:ss")
08:03:24

you need to add 24 to the 08 - but you cannot use formatting

While I do like the simplicity of this approach, I'm worried about problems when running reports. My total entered value will never be more than maybe 12 hours. However, the problem is that there will be several reports where we further average these.

For example, if we're entering their average amount of time per day, let's say they enter one value per user for each day, various values, and we need to find the average length of time for the month? Would the fact that technically adding all these days goes over 24 hours matter? Or would it not matter since it would be adding the decimal number beforehand, then dividing it by the number of days in the query/report to display the desired average value?

I always find it more flexible to enter elapsed times with three unbound fields and a single bound field for totalseconds. On the after update of the txtHH, txtMM, txtSS for each
me.totalSeconds = nz(txtHH/3600,0) + nz(txtMM/60,0) + nz(txtss). This gives the user flexibility. If they want to enter just seconds 5405, or 79 minutes and 22s, or 45 hours, 50 minutes, 60 seconds.
Only drawback it is so flexible that you cannot really error check what they really meant. BUt if you divide totalseconds/seconds in a day that then becomes a real time.

Thanks for the idea, but it would be preferable to keep the entry in one textbox if possible!

 

[namliam]

why store the data in a different format than that it is entered as ? or it is viewed as?

This isnt the 80's no more; just store time as time, rework to seconds in a report or whereever you need it to be seconds.

What you can do is "transfer" the time to a decimal using either cdbl or [yourtimefield] * 60*60*24 or the earlier datediff function

 

[ebs17]

To calculate with times such as adding, subtracting, summing, averaging, you do not need to go through integers, but values of the Date(Time) data type are internally double, i.e. a number. You can calculate on this number immediately.

?(#23:14:05# + #10:00:00#) * 1
 1,38478009259259
 
?Format((#23:14:05# + #10:00:00#) / 2, "hh:nn:ss")
16:37:03

Unfortunately, there is no standard format as a property for displaying time periods> = 24 hours. Excel has something like that.

Here you could help yourself by overwriting the VBA function Format with your own function, which then also handles hours> = 24. However, the format function creates a string, not a number that can be used directly.

Therefore, one should always distinguish between a value that one carries with him for calculation and a value for the display to the outside for a user.

Eberhard

 

[The_Doc_Man]

why do that? dates and times are stored as decimal numbers (to 10dp if I recall correctly)

CJ, a MINOR correction regarding date storage capacity.

Dates are a Typecast of DOUBLE. They store date/time entries as a binary floating-point number with 53 bits which (if you work it out carefully) is 15 full decimal digits and a 16th partial digit. When we look at dates we can analyze them this way:

Dates are running in the 40,000s these days. 40K is less than 64K so the day portion of contemporary dates will take up16 bits. As you correctly point out, there are 86,400 seconds in a day, which is more than 64K but less than 128K. That means 17 bits. Total is 33 bits and you have 53 bits. So when using a DOUBLE, you have a lot more precision than you need. In fact, it IS possible to grab a fractional time (e.g. using the TIMER() function that gives you milliseconds since midnight) and do some math to tack on that fraction. I've done that a couple of times when I was doing some event timing to debug a race condition. That works because the NOW() and TIME() functions "normalize" time to seconds, which are kept inside the Windows time and clock management functions. What you can't do is then display that fraction so easily. You have to "roll your own" formatter. BUT it is possible.

If you did the analysis for binary, you would realize that tacking on milliseconds is adding another 10 bits (2^10 = 1K) so you use 33+10 = 43 bits but still had 53 to work on.

Agree with Eberhard regarding math vs. display purposes.

 

[JackStockton]

Why not just store as DateTime with a calculated column in table or query? On the form, turn off the DatePicker and set the display format to hh:mm:ss for control.

ElapsedSeconds = Hour([Elapsed])*60*60+Minute([Elapsed])*60+Second([Elapsed])

On a report you would automatically be able to display total ElapsedSeconds and if you wanted to display in the hh:mm:ss format you would just convert back to a datetime using the formula CDate(DateAdd('s',[SumOfElapsedSeconds],Date()))

 

[Bloodlvst]

Thanks so much CJ, ebs, and The Doc.

I didn't realize I could just store times in a regular date/time format with hh:nn:ss and just run the calculations directly without a bunch of convoluted background mess. I tried a few things based on your advice, and my report is now running perfectly! Thanks so much!

 

[CJ_London]

To be clear you don’t store times as a format, you store as a date/time data type. Format is just the way it is displayed and can be changed using the format property

 

[Bloodlvst]

To be clear you don’t store times as a format, you store as a date/time data type. Format is just the way it is displayed and can be changed using the format property

Yes of course, I guess I more meant I didn't realize the date/time data type still allowed me to calculate the stuff I thought I needed to convert to integers in order to calculate.

Either way, you guys really helped me wrap my head around how Access stores time data which helped me achieve what I was looking for, and it's much appreciated!