Solved Format a number field in form of hh:mm:ss

[Pinesh]

Hello,

Attached is a MS Access Database "TimeData".
Refer "Query_1" in the database. It has a field "Total_Time". This field is a number with decimal created using a builder. This field has to be now displayed in format of hh:mm:ss. Thus a value 15.35 should be formatted / displayed as 00:15:35 (hh:mm:ss).

Can i get some help and a solution how to do the same?

Thanking you,

Regards
PM

 

[Ranman256]

that's not how format works, but use a function to convert it to 'time':
usage:
=cvtNum2Time(txtNum)

Public Function cvtNum2Time(ByVal pvNum)
Dim h, s, n
Dim i As Integer
s = 0
If IsNull(pvNum) Then Exit Function
i = InStr(pvNum, ".")
n = Int(pvNum)
If i > 0 Then s = Mid(pvNum, i + 1)
i = InStr(pvNum, ".")
h = n \ 60
n = n - (h * 60)
cvtNum2Time = Format(h, "00") & ":" & Format(n, "00") & ":" & Format(s, "00")
End Function

 

[Gasman]

15.35 as hh:mm is 15:21 ?

 

[Pinesh]

15.35 as hh:mm is 15:21 ?

The number 15.35 has to be displayed as 00:15:35 (i.e. 00 hours, 15 minutes, 35 seconds)

 

[CJ_London]

set the format property for the column to

hh:nn:ss

however you need to change your calculation to get the time in seconds (divide by a further 60). Not sure of the logic of your calculation

: ([KM_Hour]*[Distance_KM])/1440

shouldn't this be

([Distance_KM]/[KM_Hour]) to give you the time it took to cover the distance? So the first record (442 KM_Hour, 50 Distance) should be 6.6 minutes (6 mins 31 secs), not 15 minutes 35 seconds?

have to say 442 KM_Hour is a very impressive speed for a cyclist so perhaps there is other logic in play

 

[Minty]

Try a time serial function

Timeserial(Int([YourValue]), [YourValue] - Int([YourValue]))

 

[gemma-the-husky]

@Pinesh

A date is a number format, but it's different to a normal number. It is a real number x.xxxx. The integer portion represents days elapsed from a certain reference point. The decimal point represents a fraction of a day.

The 24 hour time of 15:35 is roughly 5/8 of a day (15/24hours) and therefore will be roughly 0.625 of a day.

So the suggestions are that you use some function to change your number of 15.35 into a date/time value equivalent to a time of 15:35 . You can then do calculations with the time, using intrinsic access date functions. for example you have said that 15.35 represent 3:35pm, or But if you tried to add 5:45 elapsed time to 15:35 in your system it's not easy because you get 20:80 which isn't a valid time. Hence all the suggestions others have made.

 

[Pinesh]

Ranman256,
Gasman,
CJ_London,
Minty,
Gemma-the-husky

Thanks a ton for the inputs and guidance.
Have been able to solve it finally.
Seems there was data input error as well as instructions error.
A decimal was missed out that lead to the KM_Hour being input as 442 KM/hour instead of 4.42 per hour
And the instructions (Clarified now) were that to convert the Speed x distance into Time format

Used the function : Format([Total_Time], "hh:nn:ss")
Worked perfectly.

Again thanks for all the guidance.
Regards / Pinesh

 

[Gasman]

Hmm, what is to stop that happening again?
Might be worth having some min and max values stored that could be examined to see if the data is outside recognised ranges?
What if I have entered 4442? Admittedly the user *should* spot their mistake, but as I have had colleagues pasting postcode as surnames, even first names as surnames, then it is best to stop what you can, surely?

 

[CJ_London]

KM_Hour being input as 442 KM/hour instead of 4.42 per hour

so these aren't super fast cyclists, they are zimmer frame users?

And the instructions (Clarified now) were that to convert the Speed x distance into Time format

still doesn't make sense to me but good luck with your project

 

[Pat Hartman]

We're missing something important. Please show us how 01:14:35 is actually stored. is it a string with two colons? Or is it a numeric integer in seconds? Or is it a DateTime data type.