Time formatting question

[Wylie C]

I have a time field that I want to store hh:mm:ss. I can set the custom
format to 99:00:00 and the user can input the time but no matter what I do
the table only displays hh:mm.

Thank you.

 

[Ken Snell [MVP]]

In the table's design view, set the field's Format to Short Time or enter
hh:nn:ss in the Format property.

 

[Sprinks]

To display the time in hh:mm:ss format when looking at the Table directly,
set the field's format to Long Time in table design view.

Sprinks

 

[Ken Snell [MVP]]

Oops. Sprinks is correct....it's Long Time, not Short Time.

 

[Wylie C]

Thanks both but I am still having problems..
When I set the field format to hh:nn:ss and the input mask to 99:99:00 it
seems to display and store the time in hours, minutes and seconds.

I have set the properties on the form to match the table but the value
returned afte data is entered shows am or pm....

What I need to do is the user enters a starting time and an ending time and
I need to calculate the average speed. Below is the code I have been working
with. I started with only hours and minutes input from the user and am trying
to change to the hh:mm:ss. The code works until I get to the seconds part:

Private Sub Time_Exit(Cancel As Integer)


Dim MyETime, MyEMinute, MyEhour, MyESeconds, ETotlTime
MyETime = Me.Time

If [OdometerEnd] = "0" Or [OdometerEnd] = "" Then
[Route#].SetFocus
Else
'Assign a time
MyEhour = Hour(MyETime) 'MyEHour contains hours
MyEMinute = Minute(MyETime) 'MyEMinute contains minutes
MyESeconds = Seconds(MyTime) 'MyESeconds contains seconds

ETotlTime = (MyEhour * 60) + (MyEMinute * 60) + MyESeconds
Me!ETAveSpeed = ([Mileage] / ETotlTime) * 360
[RideTime].SetFocus
End If

End Sub

I get the error at the MyESeconds =
Debugging MyEtime at line 3 shows hh:mm:ss with am/pm

 

[Rick B]

If I am reading your question correctly...


You said you want to store a TIME, not a DURATION. They are two different
things.

TIME is a specific point in time (1 o'clock, January 15). TIME in this case
would never be greater than 24:00:00.

DURATION is a length of time spent on a task, or time remaining until an
event. This could be any number and can be greater than 24 hours. The key
here is that this is a NUMBER, not a TIME. Once you do your calculation,
you will get the results in elapses seconds and you can do the division to
break it out into hours, minutes, and seconds. 1,000 seconds equals 16
minutes, 40 seconds, for example.


Hope that helps,

Rick B

Thanks both but I am still having problems..
When I set the field format to hh:nn:ss and the input mask to 99:99:00 it
seems to display and store the time in hours, minutes and seconds.

I have set the properties on the form to match the table but the value
returned afte data is entered shows am or pm....

What I need to do is the user enters a starting time and an ending time and
I need to calculate the average speed. Below is the code I have been working
with. I started with only hours and minutes input from the user and am trying
to change to the hh:mm:ss. The code works until I get to the seconds part:

Private Sub Time_Exit(Cancel As Integer)


Dim MyETime, MyEMinute, MyEhour, MyESeconds, ETotlTime
MyETime = Me.Time

If [OdometerEnd] = "0" Or [OdometerEnd] = "" Then
[Route#].SetFocus
Else
'Assign a time
MyEhour = Hour(MyETime) 'MyEHour contains hours
MyEMinute = Minute(MyETime) 'MyEMinute contains minutes
MyESeconds = Seconds(MyTime) 'MyESeconds contains seconds

ETotlTime = (MyEhour * 60) + (MyEMinute * 60) + MyESeconds
Me!ETAveSpeed = ([Mileage] / ETotlTime) * 360
[RideTime].SetFocus
End If

End Sub

I get the error at the MyESeconds =
Debugging MyEtime at line 3 shows hh:mm:ss with am/pm

Oops. Sprinks is correct....it's Long Time, not Short Time.

 

[Wylie C]

Rick,
I know I should be getting this but I am not. I have changed the field in
the table to Number, single. Input mask 99:99:00 and the same on the form.
When I run the code, the time stored in the table has no formatting to
separate the hrs, min, sec. If I enter 01:00:00 the number the code reads is
10000. How can I store the value in the table as hh:nn:ss?

If I am reading your question correctly...


You said you want to store a TIME, not a DURATION. They are two different
things.

TIME is a specific point in time (1 o'clock, January 15). TIME in this case
would never be greater than 24:00:00.

DURATION is a length of time spent on a task, or time remaining until an
event. This could be any number and can be greater than 24 hours. The key
here is that this is a NUMBER, not a TIME. Once you do your calculation,
you will get the results in elapses seconds and you can do the division to
break it out into hours, minutes, and seconds. 1,000 seconds equals 16
minutes, 40 seconds, for example.


Hope that helps,

Rick B

Thanks both but I am still having problems..
When I set the field format to hh:nn:ss and the input mask to 99:99:00 it
seems to display and store the time in hours, minutes and seconds.

I have set the properties on the form to match the table but the value
returned afte data is entered shows am or pm....

What I need to do is the user enters a starting time and an ending time and
I need to calculate the average speed. Below is the code I have been working
with. I started with only hours and minutes input from the user and am trying
to change to the hh:mm:ss. The code works until I get to the seconds part:

Private Sub Time_Exit(Cancel As Integer)


Dim MyETime, MyEMinute, MyEhour, MyESeconds, ETotlTime
MyETime = Me.Time

If [OdometerEnd] = "0" Or [OdometerEnd] = "" Then
[Route#].SetFocus
Else
'Assign a time
MyEhour = Hour(MyETime) 'MyEHour contains hours
MyEMinute = Minute(MyETime) 'MyEMinute contains minutes
MyESeconds = Seconds(MyTime) 'MyESeconds contains seconds

ETotlTime = (MyEhour * 60) + (MyEMinute * 60) + MyESeconds
Me!ETAveSpeed = ([Mileage] / ETotlTime) * 360
[RideTime].SetFocus
End If

End Sub

I get the error at the MyESeconds =
Debugging MyEtime at line 3 shows hh:mm:ss with am/pm

Oops. Sprinks is correct....it's Long Time, not Short Time.

--

Ken Snell
<MS ACCESS MVP>

In the table's design view, set the field's Format to Short Time or enter
hh:nn:ss in the Format property.

--

Ken Snell
<MS ACCESS MVP>

I have a time field that I want to store hh:mm:ss. I can set the custom
format to 99:00:00 and the user can input the time but no matter what I
do
the table only displays hh:mm.

Thank you.

 

[Rick B]

You can't. It is stored as seconds. To format it, you will have to do some
math.

Here is an example of storing minutes as hours and minutes. In other words,
120 would display 2:00.

You will have to modify it and place it in an unbound field on your form or
report...

=[SomeFieldName]\60 & Format([SomeFieldName] Mod 60, "\:00")


Hope that helps.

Rick B

Rick,
I know I should be getting this but I am not. I have changed the field in
the table to Number, single. Input mask 99:99:00 and the same on the form.
When I run the code, the time stored in the table has no formatting to
separate the hrs, min, sec. If I enter 01:00:00 the number the code reads is
10000. How can I store the value in the table as hh:nn:ss?

If I am reading your question correctly...


You said you want to store a TIME, not a DURATION. They are two different
things.

TIME is a specific point in time (1 o'clock, January 15). TIME in this case
would never be greater than 24:00:00.

DURATION is a length of time spent on a task, or time remaining until an
event. This could be any number and can be greater than 24 hours. The key
here is that this is a NUMBER, not a TIME. Once you do your calculation,
you will get the results in elapses seconds and you can do the division to
break it out into hours, minutes, and seconds. 1,000 seconds equals 16
minutes, 40 seconds, for example.


Hope that helps,

Rick B

Thanks both but I am still having problems..
When I set the field format to hh:nn:ss and the input mask to 99:99:00 it
seems to display and store the time in hours, minutes and seconds.

I have set the properties on the form to match the table but the value
returned afte data is entered shows am or pm....

What I need to do is the user enters a starting time and an ending

time
and

I need to calculate the average speed. Below is the code I have been working
with. I started with only hours and minutes input from the user and am trying
to change to the hh:mm:ss. The code works until I get to the seconds part:

Private Sub Time_Exit(Cancel As Integer)


Dim MyETime, MyEMinute, MyEhour, MyESeconds, ETotlTime
MyETime = Me.Time

If [OdometerEnd] = "0" Or [OdometerEnd] = "" Then
[Route#].SetFocus
Else
'Assign a time
MyEhour = Hour(MyETime) 'MyEHour contains hours
MyEMinute = Minute(MyETime) 'MyEMinute contains minutes
MyESeconds = Seconds(MyTime) 'MyESeconds contains seconds

ETotlTime = (MyEhour * 60) + (MyEMinute * 60) + MyESeconds
Me!ETAveSpeed = ([Mileage] / ETotlTime) * 360
[RideTime].SetFocus
End If

End Sub

I get the error at the MyESeconds =
Debugging MyEtime at line 3 shows hh:mm:ss with am/pm

:

Oops. Sprinks is correct....it's Long Time, not Short Time.

--

Ken Snell
<MS ACCESS MVP>

In the table's design view, set the field's Format to Short Time

or
enter

hh:nn:ss in the Format property.

--

Ken Snell
<MS ACCESS MVP>

I have a time field that I want to store hh:mm:ss. I can set the custom
format to 99:00:00 and the user can input the time but no matter

what
I

do
the table only displays hh:mm.

Thank you.