Access 2007 + Time Formats - Sports Clocking

[Sayth]

I have a database I am starting to create. I need some guidance in how to
best create a time format for a "sports watch". What I need to record is
minutes:seconds:hundredths, however the times will need be manually inputted
or imported.

There will also be basic calculations performed on times e.g car a time for
lap 1 is 1:22:30 and this is 00:01:27 outside best time. So store times and
then be able to calculate differences in time and store them.

There seems to be two basic ways to go 1) create an input mask and create a
text field as mm:ss:uu, only I am unsure how to validate the inputs so that
seconds greater than 60 cannot be created or hundredths greater than 99.
Seems may have calculations complications to.

Create a table/template/format so that each time is inputted into separate
fields, so that minutes has its own field seconds etc. Then have a field
where it adds previous fields so ([mm]+[ss]+[uu]). Bit beyond my skill level
but makes more sense, would like more information on this approach if it is
better?

Any ideas? Other ideas besides my two above appreciated.

 

[John W. Vinson]

I have a database I am starting to create. I need some guidance in how to
best create a time format for a "sports watch". What I need to record is
minutes:seconds:hundredths, however the times will need be manually inputted
or imported.

There will also be basic calculations performed on times e.g car a time for
lap 1 is 1:22:30 and this is 00:01:27 outside best time. So store times and
then be able to calculate differences in time and store them.

There seems to be two basic ways to go 1) create an input mask and create a
text field as mm:ss:uu, only I am unsure how to validate the inputs so that
seconds greater than 60 cannot be created or hundredths greater than 99.
Seems may have calculations complications to.

Create a table/template/format so that each time is inputted into separate
fields, so that minutes has its own field seconds etc. Then have a field
where it adds previous fields so ([mm]+[ss]+[uu]). Bit beyond my skill level
but makes more sense, would like more information on this approach if it is
better?

Any ideas? Other ideas besides my two above appreciated.

An Access Date/Time value will not allow any precision finer than seconds, as
you've evidently gathered. I would suggest storing the number as seconds and
fractions of seconds, in (odd as it may sound) a Currency field - which allows
four decimal places and has no roundoff error. You can have three unbound
textboxes on a Form, for hours, minutes, and seconds (e.g. 43.92) and a fourth
bound to this currency field. You can put code in the AfterUpdate event of
each of the time portion textboxes like:

Private Sub txtMinutes_AfterUpdate()
Me!txtRacetime = NZ(Me!txtHours)*3600 + NZ(Me!txtMinutes) * 60 _
+ NZ(Me!txtSeconds)
End Sub

You can also put validation in the BeforeUpdate event of each textbox to
ensure that the value is appropriate.

 

[flebber]

I have a database I am starting to create. I need some guidance in how to
best create a time format for a "sports watch". What I need to record is
minutes:seconds:hundredths, however the times will need be manually inputted
or imported.

There will also be basic calculations performed on times e.g car a time for
lap 1 is 1:22:30 and this is 00:01:27 outside best time. So store times and
then be able to calculate differences in time and store them.

There seems to be two basic ways to go 1) create an input mask and create a
text field as mm:ss:uu, only I am unsure how to validate the inputs so that
seconds greater than 60 cannot be created or hundredths greater than 99.
Seems may have calculations complications to.

Create a table/template/format so that each time is inputted into separate
fields, so that minutes has its own field seconds etc. Then have a field
where it adds previous fields so ([mm]+[ss]+[uu]). Bit beyond my skill level
but makes more sense, would like more information on this approach if itis
better?

Any ideas? Other ideas besides my two above appreciated.

An Access Date/Time value will not allow any precision finer than seconds, as
you've evidently gathered. I would suggest storing the number as seconds and
fractions of seconds, in (odd as it may sound) a Currency field - which allows
four decimal places and has no roundoff error. You can have three unbound
textboxes on a Form, for hours, minutes, and seconds (e.g. 43.92) and a fourth
bound to this currency field. You can put code in the AfterUpdate event of
each of the time portion textboxes like:

Private Sub txtMinutes_AfterUpdate()
Me!txtRacetime = NZ(Me!txtHours)*3600 + NZ(Me!txtMinutes) * 60  _
     + NZ(Me!txtSeconds)
End Sub

You can also put validation in the BeforeUpdate event of each textbox to
ensure that the value is appropriate.

With regard to your code

Private Sub txtMinutes_AfterUpdate()
Me!txtRacetime = NZ(Me!txtHours)*3600 + NZ(Me!txtMinutes) * 60 _
+ NZ(Me!txtSeconds)
End Sub

Where exactly do I insert code, in the VBA editor when creating the
form? Is that correct

 

[John W. Vinson]

Where exactly do I insert code, in the VBA editor when creating the
form? Is that correct

Create the Form; select each of the three hours/minutes/seconds textboxes in
turn and view their Properties. On the "Events" tab select the After Update
line and click the ... icon by it; choose "Code Builder" and insert this code
(using your own actual control names of course).

 

[david]

An Access Date/Time value will not allow any precision finer than seconds,

as

That is to say, Access has no way to enter or display a precision finer
than seconds, so you will have to build your own.

You can use an Access Date/Time value to store the data if you wish,
down to a precision of better than 1E-300, as Access does if you
connect to a SQL Server DateTime value.

But there may be no advantage to doing so, because of the lack of
input/output formats.

(david)

I have a database I am starting to create. I need some guidance in how to
best create a time format for a "sports watch". What I need to record is
minutes:seconds:hundredths, however the times will need be manually
inputted
or imported.

There will also be basic calculations performed on times e.g car a time
for
lap 1 is 1:22:30 and this is 00:01:27 outside best time. So store times
and
then be able to calculate differences in time and store them.

There seems to be two basic ways to go 1) create an input mask and create
a
text field as mm:ss:uu, only I am unsure how to validate the inputs so
that
seconds greater than 60 cannot be created or hundredths greater than 99.
Seems may have calculations complications to.

Create a table/template/format so that each time is inputted into separate
fields, so that minutes has its own field seconds etc. Then have a field
where it adds previous fields so ([mm]+[ss]+[uu]). Bit beyond my skill
level
but makes more sense, would like more information on this approach if it
is
better?

Any ideas? Other ideas besides my two above appreciated.

An Access Date/Time value will not allow any precision finer than seconds,
as
you've evidently gathered. I would suggest storing the number as seconds
and
fractions of seconds, in (odd as it may sound) a Currency field - which
allows
four decimal places and has no roundoff error. You can have three unbound
textboxes on a Form, for hours, minutes, and seconds (e.g. 43.92) and a
fourth
bound to this currency field. You can put code in the AfterUpdate event of
each of the time portion textboxes like:

Private Sub txtMinutes_AfterUpdate()
Me!txtRacetime = NZ(Me!txtHours)*3600 + NZ(Me!txtMinutes) * 60 _
+ NZ(Me!txtSeconds)
End Sub

You can also put validation in the BeforeUpdate event of each textbox to
ensure that the value is appropriate.

 

[flebber]

An Access Date/Time value will not allow any precision finer than seconds,
as

That is to say, Access has no way to enter or display a precision finer
than seconds, so you will have to build your own.

You can use an Access Date/Time value to store the data if you wish,
down to a precision of  better than 1E-300, as Access does if you
connect to a SQL Server DateTime value.

But there may be no advantage to doing so,  because of the lack of
input/output formats.

(david)

@discussions.microsoft.com>
wrote:

I have a database I am starting to create. I need some guidance in how to
best create a time format for a "sports watch". What I need to record is
minutes:seconds:hundredths, however the times will need be manually
inputted
or imported.
There will also be basic calculations performed on times e.g car a time
for
lap 1 is 1:22:30 and this is 00:01:27 outside best time. So store times
and
then be able to calculate differences in time and store them.
There seems to be two basic ways to go 1) create an input mask and create
a
text field as mm:ss:uu, only I am unsure how to validate the inputs so
that
seconds greater than 60 cannot be created or hundredths greater than 99..
Seems may have calculations complications to.
Create a table/template/format so that each time is inputted into separate
fields, so that minutes has its own field seconds etc. Then have a field
where it adds previous fields so ([mm]+[ss]+[uu]). Bit beyond my skill
level
but makes more sense, would like more information on this approach if it
is
better?
Any ideas? Other ideas besides my two above appreciated.

An Access Date/Time value will not allow any precision finer than seconds,
as
you've evidently gathered. I would suggest storing the number as seconds
and
fractions of seconds, in (odd as it may sound) a Currency field - which
allows
four decimal places and has no roundoff error. You can have three unbound
textboxes on a Form, for hours, minutes, and seconds (e.g. 43.92) and a
fourth
bound to this currency field. You can put code in the AfterUpdate eventof
each of the time portion textboxes like:

Private Sub txtMinutes_AfterUpdate()
Me!txtRacetime = NZ(Me!txtHours)*3600 + NZ(Me!txtMinutes) * 60  _
    + NZ(Me!txtSeconds)
End Sub

You can also put validation in the BeforeUpdate event of each textbox to
ensure that the value is appropriate.
--

            John W. Vinson [MVP]

Thank you am trying to follow but I am not yet as advanced as you. So
the private sub names and fields change with each sub section

so seconds is

Private Sub txtSeconds_AfterUpdate()
Me!txtRacetime = NZ(Me!txMinutes)*60 + NZ(Me!txtSaeconds) * 60 _
+ NZ(Me!txtSeconds)
End Sub

That leaves me then with the input from I believe 2 unbound form boxes
for me being "minutes" and "seconds" and hundredths of seconds
recorded in a currency field.

Am I then amalgamating the fields to one field or how will I perform
calculations between time?

([txtMinutes]+[txtSeconds]+[hundredths]) + or - a similar combined
number?

 

[John W. Vinson]

Thank you am trying to follow but I am not yet as advanced as you. So
the private sub names and fields change with each sub section

so seconds is

Private Sub txtSeconds_AfterUpdate()
Me!txtRacetime = NZ(Me!txMinutes)*60 + NZ(Me!txtSaeconds) * 60 _
+ NZ(Me!txtSeconds)
End Sub

That leaves me then with the input from I believe 2 unbound form boxes
for me being "minutes" and "seconds" and hundredths of seconds
recorded in a currency field.

Am I then amalgamating the fields to one field or how will I perform
calculations between time?

([txtMinutes]+[txtSeconds]+[hundredths]) + or - a similar combined
number?

I'm suggesting storing the seconds and hundredths in one number - I don't see
any benefit to entering 13 (seconds) in one textbox and 41 (hundredths) in a
different textbox, vs. typing 13.41 into a single textbox.

The expression I posted:

Private Sub txtMinutes_AfterUpdate()
Me!txtRacetime = NZ(Me!txtHours)*3600 + NZ(Me!txtMinutes) * 60 _
+ NZ(Me!txtSeconds)
End Sub

generates the combined number. The NZ (Null To Zero) function prevents errors
when a textbox is empty: anything plus NULL gives NULL, and the NZ function
converts the null value into a zero. There are 3600 seconds in an hour, and 60
in a minute, so I'm multiplying the entered number of hours by 3600 and the
entered number of minutes by 60, and adding those two products to the number
of seconds in the third textbox.

What I'm suggesting is that you have this identical expression in the
AfterUpdate event of the three textboxes, which I named txtHours, txtMinutes,
and txtSeconds. If you change any one of them, it will recalculate the sum of
the three and store the result into the textbox named txtRacetime, which would
be bound to the (currency, or decimal, or Double) race time field in your
table. It might be better to explicitly convert the calculated result to your
desired datatype: e.g. if the race time is a Currency field, use

Private Sub txtMinutes_AfterUpdate()
Me!txtRacetime = CCur(NZ(Me!txtHours)*3600) _
+ CCur(NZ(Me!txtMinutes) * 60) _
+ CCur(NZ(Me!txtSeconds))
End Sub

You would also want to do the reverse - display the hours, minutes, and
seconds when you navigate to a record which already has a time. To do so put
code in the form's Current event:

Private Sub Form_Current()
If Not IsNull(Me!txtRacetime) Then
Me!txtHours = Me!txtRacetime \ 3600
Me!txtMinutes = Me!txtRaceTime \ 60 MOD 60
Me!txtSeconds = Me!txtRaceTime - 60 * (Me!txtRacetime \ 60)
End If
End Sub

The \ isn't a typo - it's the integer divide operator, so a a racetime of
128.24 seconds, integer divided by 60, is 2 minutes. The last expression gets
the remaining 8.24 seconds.

 

[a a r o n . k e m p f]

I'm not sure that Access datatypes have the level of precision that
you're looking for

 

[flebber]

I'm not sure that Access datatypes have the level of precision that
you're looking for

I have a database I am starting to create. I need some guidance in how to
best create a time format for a "sports watch". What I need to record is
minutes:seconds:hundredths, however the times will need be manually inputted
or imported.

There will also be basic calculations performed on times e.g car a timefor
lap 1 is 1:22:30 and this is 00:01:27 outside best time. So store timesand
then be able to calculate differences in time and store them.

There seems to be two basic ways to go 1) create an input mask and create a
text field as mm:ss:uu, only I am unsure how to validate the inputs so that
seconds greater than 60 cannot be created or hundredths greater than 99..
Seems may have calculations complications to.

Create a table/template/format so that each time is inputted into separate
fields, so that minutes has its own field seconds etc. Then have a field
where it adds previous fields so ([mm]+[ss]+[uu]). Bit beyond my skill level
but makes more sense, would like more information on this approach if it is
better?

Any ideas? Other ideas besides my two above appreciated.

Maybe not, but Access has many other advantages. The fact that a
"duration" data-type doesn't exist is strange to me as a newbie since
a wide range of sports use them, cooking and recipes rely on duration
though maybe not as exacting, and there are plenty of business uses,
call centres (handle times/durations) etc. But testing the solutions I
am learning and should be able to overcome the time/duration
limitation.

 

[Sayth]

Thank you am trying to follow but I am not yet as advanced as you. So
the private sub names and fields change with each sub section

so seconds is

Private Sub txtSeconds_AfterUpdate()
Me!txtRacetime = NZ(Me!txMinutes)*60 + NZ(Me!txtSaeconds) * 60 _
+ NZ(Me!txtSeconds)
End Sub

That leaves me then with the input from I believe 2 unbound form boxes
for me being "minutes" and "seconds" and hundredths of seconds
recorded in a currency field.

Am I then amalgamating the fields to one field or how will I perform
calculations between time?

([txtMinutes]+[txtSeconds]+[hundredths]) + or - a similar combined
number?

I'm suggesting storing the seconds and hundredths in one number - I don't see
any benefit to entering 13 (seconds) in one textbox and 41 (hundredths) in a
different textbox, vs. typing 13.41 into a single textbox.

The expression I posted:

Private Sub txtMinutes_AfterUpdate()
Me!txtRacetime = NZ(Me!txtHours)*3600 + NZ(Me!txtMinutes) * 60 _
+ NZ(Me!txtSeconds)
End Sub

generates the combined number. The NZ (Null To Zero) function prevents errors
when a textbox is empty: anything plus NULL gives NULL, and the NZ function
converts the null value into a zero. There are 3600 seconds in an hour, and 60
in a minute, so I'm multiplying the entered number of hours by 3600 and the
entered number of minutes by 60, and adding those two products to the number
of seconds in the third textbox.

What I'm suggesting is that you have this identical expression in the
AfterUpdate event of the three textboxes, which I named txtHours, txtMinutes,
and txtSeconds. If you change any one of them, it will recalculate the sum of
the three and store the result into the textbox named txtRacetime, which would
be bound to the (currency, or decimal, or Double) race time field in your
table. It might be better to explicitly convert the calculated result to your
desired datatype: e.g. if the race time is a Currency field, use

Private Sub txtMinutes_AfterUpdate()
Me!txtRacetime = CCur(NZ(Me!txtHours)*3600) _
+ CCur(NZ(Me!txtMinutes) * 60) _
+ CCur(NZ(Me!txtSeconds))
End Sub

You would also want to do the reverse - display the hours, minutes, and
seconds when you navigate to a record which already has a time. To do so put
code in the form's Current event:

Private Sub Form_Current()
If Not IsNull(Me!txtRacetime) Then
Me!txtHours = Me!txtRacetime \ 3600
Me!txtMinutes = Me!txtRaceTime \ 60 MOD 60
Me!txtSeconds = Me!txtRaceTime - 60 * (Me!txtRacetime \ 60)
End If
End Sub

The \ isn't a typo - it's the integer divide operator, so a a racetime of
128.24 seconds, integer divided by 60, is 2 minutes. The last expression gets
the remaining 8.24 seconds.

The penny finally dropped as to how exactly this was working. Got it.
Awesome thanks Heaps John for your help.

Your time and effort very much appreciated.

 

[Sayth]

I'm not sure that Access datatypes have the level of precision that
you're looking for

I have a database I am starting to create. I need some guidance in how to
best create a time format for a "sports watch". What I need to record is
minutes:seconds:hundredths, however the times will need be manually inputted
or imported.

There will also be basic calculations performed on times e.g car a time for
lap 1 is 1:22:30 and this is 00:01:27 outside best time. So store times and
then be able to calculate differences in time and store them.

There seems to be two basic ways to go 1) create an input mask and create a
text field as mm:ss:uu, only I am unsure how to validate the inputs so that
seconds greater than 60 cannot be created or hundredths greater than 99..
Seems may have calculations complications to.

Create a table/template/format so that each time is inputted into separate
fields, so that minutes has its own field seconds etc. Then have a field
where it adds previous fields so ([mm]+[ss]+[uu]). Bit beyond my skill level
but makes more sense, would like more information on this approach if it is
better?

Any ideas? Other ideas besides my two above appreciated.

Maybe not, but Access has many other advantages. The fact that a
"duration" data-type doesn't exist is strange to me as a newbie since
a wide range of sports use them, cooking and recipes rely on duration
though maybe not as exacting, and there are plenty of business uses,
call centres (handle times/durations) etc. But testing the solutions I
am learning and should be able to overcome the time/duration
limitation.
.

Found this article on SQL, most of the content seems relevant to Access as
well. Interesting that SQL has no data-type for durations as well.

This is a very good article and very much reinforces your method John.

http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server

 

[Sayth]

Thank you am trying to follow but I am not yet as advanced as you. So
the private sub names and fields change with each sub section

so seconds is

Private Sub txtSeconds_AfterUpdate()
Me!txtRacetime = NZ(Me!txMinutes)*60 + NZ(Me!txtSaeconds) * 60 _
+ NZ(Me!txtSeconds)
End Sub

That leaves me then with the input from I believe 2 unbound form boxes
for me being "minutes" and "seconds" and hundredths of seconds
recorded in a currency field.

Am I then amalgamating the fields to one field or how will I perform
calculations between time?

([txtMinutes]+[txtSeconds]+[hundredths]) + or - a similar combined
number?

I'm suggesting storing the seconds and hundredths in one number - I don't see
any benefit to entering 13 (seconds) in one textbox and 41 (hundredths) in a
different textbox, vs. typing 13.41 into a single textbox.

The expression I posted:

Private Sub txtMinutes_AfterUpdate()
Me!txtRacetime = NZ(Me!txtHours)*3600 + NZ(Me!txtMinutes) * 60 _
+ NZ(Me!txtSeconds)
End Sub

generates the combined number. The NZ (Null To Zero) function prevents errors
when a textbox is empty: anything plus NULL gives NULL, and the NZ function
converts the null value into a zero. There are 3600 seconds in an hour, and 60
in a minute, so I'm multiplying the entered number of hours by 3600 and the
entered number of minutes by 60, and adding those two products to the number
of seconds in the third textbox.

What I'm suggesting is that you have this identical expression in the
AfterUpdate event of the three textboxes, which I named txtHours, txtMinutes,
and txtSeconds. If you change any one of them, it will recalculate the sum of
the three and store the result into the textbox named txtRacetime, which would
be bound to the (currency, or decimal, or Double) race time field in your
table. It might be better to explicitly convert the calculated result to your
desired datatype: e.g. if the race time is a Currency field, use

Private Sub txtMinutes_AfterUpdate()
Me!txtRacetime = CCur(NZ(Me!txtHours)*3600) _
+ CCur(NZ(Me!txtMinutes) * 60) _
+ CCur(NZ(Me!txtSeconds))
End Sub

You would also want to do the reverse - display the hours, minutes, and
seconds when you navigate to a record which already has a time. To do so put
code in the form's Current event:

Private Sub Form_Current()
If Not IsNull(Me!txtRacetime) Then
Me!txtHours = Me!txtRacetime \ 3600
Me!txtMinutes = Me!txtRaceTime \ 60 MOD 60
Me!txtSeconds = Me!txtRaceTime - 60 * (Me!txtRacetime \ 60)
End If
End Sub

The \ isn't a typo - it's the integer divide operator, so a a racetime of
128.24 seconds, integer divided by 60, is 2 minutes. The last expression gets
the remaining 8.24 seconds.

The penny finally dropped as to how exactly this was working. Got it.
Awesome thanks Heaps John for your help.

Your time and effort very much appreciated.

Re: Above I found two articles that reinforce what you have already advised
but they are very helpful as well, they are SQL artciles but seem quite
relevant to Access.

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server

 

[david]

http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server

But note that people DO work with months and years :~) Rent and
Financial Instruments are two areas that spring to mind, so some
people need to and do calculate duration in months and years.

(david)

On Mon, 15 Mar 2010 04:54:35 -0700 (PDT), flebber
<[email protected]>
wrote:

Thank you am trying to follow but I am not yet as advanced as you. So
the private sub names and fields change with each sub section

so seconds is

Private Sub txtSeconds_AfterUpdate()
Me!txtRacetime = NZ(Me!txMinutes)*60 + NZ(Me!txtSaeconds) * 60 _
+ NZ(Me!txtSeconds)
End Sub

That leaves me then with the input from I believe 2 unbound form boxes
for me being "minutes" and "seconds" and hundredths of seconds
recorded in a currency field.

Am I then amalgamating the fields to one field or how will I perform
calculations between time?

([txtMinutes]+[txtSeconds]+[hundredths]) + or - a similar combined
number?

I'm suggesting storing the seconds and hundredths in one number - I
don't see
any benefit to entering 13 (seconds) in one textbox and 41 (hundredths)
in a
different textbox, vs. typing 13.41 into a single textbox.

The expression I posted:

Private Sub txtMinutes_AfterUpdate()
Me!txtRacetime = NZ(Me!txtHours)*3600 + NZ(Me!txtMinutes) * 60 _
+ NZ(Me!txtSeconds)
End Sub

generates the combined number. The NZ (Null To Zero) function prevents
errors
when a textbox is empty: anything plus NULL gives NULL, and the NZ
function
converts the null value into a zero. There are 3600 seconds in an hour,
and 60
in a minute, so I'm multiplying the entered number of hours by 3600 and
the
entered number of minutes by 60, and adding those two products to the
number
of seconds in the third textbox.

What I'm suggesting is that you have this identical expression in the
AfterUpdate event of the three textboxes, which I named txtHours,
txtMinutes,
and txtSeconds. If you change any one of them, it will recalculate the
sum of
the three and store the result into the textbox named txtRacetime,
which would
be bound to the (currency, or decimal, or Double) race time field in
your
table. It might be better to explicitly convert the calculated result
to your
desired datatype: e.g. if the race time is a Currency field, use

Private Sub txtMinutes_AfterUpdate()
Me!txtRacetime = CCur(NZ(Me!txtHours)*3600) _
+ CCur(NZ(Me!txtMinutes) * 60) _
+ CCur(NZ(Me!txtSeconds))
End Sub

You would also want to do the reverse - display the hours, minutes, and
seconds when you navigate to a record which already has a time. To do
so put
code in the form's Current event:

Private Sub Form_Current()
If Not IsNull(Me!txtRacetime) Then
Me!txtHours = Me!txtRacetime \ 3600
Me!txtMinutes = Me!txtRaceTime \ 60 MOD 60
Me!txtSeconds = Me!txtRaceTime - 60 * (Me!txtRacetime \ 60)
End If
End Sub

The \ isn't a typo - it's the integer divide operator, so a a racetime
of
128.24 seconds, integer divided by 60, is 2 minutes. The last
expression gets
the remaining 8.24 seconds.

The penny finally dropped as to how exactly this was working. Got it.
Awesome thanks Heaps John for your help.

Your time and effort very much appreciated.

Re: Above I found two articles that reinforce what you have already
advised
but they are very helpful as well, they are SQL artciles but seem quite
relevant to Access.

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server