Access 2007 Time format - Sports Lap timing and calculating

[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 it is better?

Any ideas? Other ideas besides my two above appreciated.

 

[Jeff Boyce]

I don't believe Access has a native "Time" data type. Every example I've
seen and used for this task involves storing the time in the lowest unit of
measure and using a query to calculate, on the fly, and format for display
as "minutes:seconds;hundredths".

If you elect to use separate fields for each (i.e., xx minutes, yy seconds,
zz hundredths), you'll still need a way to "do math" the numbers, which will
involve calculating using the lowest unit of measure and formatting for
display.

Have you searched on-line for this kind of algorithm/procedure? Have you
checked at mvps.org/access?

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[flebber]

I don't believe Access has a native "Time" data type.  Every example I've
seen and used for this task involves storing the time in the lowest unit of
measure and using a query to calculate, on the fly, and format for display
as "minutes:seconds;hundredths".

If you elect to use separate fields for each (i.e., xx minutes, yy seconds,
zz hundredths), you'll still need a way to "do math" the numbers, which will
involve calculating using the lowest unit of measure and formatting for
display.

Have you searched on-line for this kind of algorithm/procedure?  Have you
checked at mvps.org/access?

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentionedin
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

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.

i looked at mvps.org/access. It doesn't actually have the time
selection or capturing methodology, but it does have plenty of other
good information such as the evils of lookup tables
http://mvps.org/access/lookupfields.htm , which causes me some concern
as lookups is how I have been able to get things working. I obviously
need another methodology.

 

[Arvin Meyer [MVP]]

Time is not duration, and that's really what you need to store. Have you
thought of storing milliseconds then formatting them into the time display
that you are looking for? Here's some code that may help you:

VBA time does not resolve to the millisecond. You have to use Win32API.
Sample code follows:

' Declarations section
' System time - coorindated universal time (UTC)

Private Type SYSTEMTIME
intYear As Integer
intMonth As Integer
intDayOfWeek As Integer
intDay As Integer
intHour As Integer
intMinute As Integer
intSecond As Integer
intMilliseconds As Integer
End Type

Declare Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
'---------------------------------------------------------------------------
' Systemtime

Public Function GetTimeUTC() As String
'Returns a string containing UTC time to the millisecond
'that is unique and not internationalized
' SystemTime structure is loaded in declarations section

' Procedure dimensions
Dim lngType As Long
Dim stSystemTime As SYSTEMTIME
Call GetSystemTime(stSystemTime)

With stSystemTime
GetTimeUTC = Format(.intHour, "00") + ":" + Format(.intMinute, "00") _
+ ":" + Format(.intSecond, "00") + ":" + Format(.intMilliseconds, "000")
End With

End Function

 

[flebber]

Time is not duration, and that's really what you need to store. Have you
thought of storing milliseconds then formatting them into the time display
that you are looking for? Here's some code that may help you:

VBA time does not resolve to the millisecond. You have to use Win32API.
Sample code follows:

' Declarations section
' System time - coorindated universal time (UTC)

Private Type SYSTEMTIME
     intYear As Integer
     intMonth As Integer
     intDayOfWeek As Integer
     intDay As Integer
     intHour As Integer
     intMinute As Integer
     intSecond As Integer
     intMilliseconds As Integer
End Type

Declare Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
'---------------------------------------------------------------------------
' Systemtime

Public Function GetTimeUTC() As String
'Returns a string containing UTC time to the millisecond
'that is unique and not internationalized
  ' SystemTime structure is loaded in declarations section

' Procedure dimensions
Dim lngType As Long
Dim stSystemTime As SYSTEMTIME
Call GetSystemTime(stSystemTime)

With stSystemTime
  GetTimeUTC = Format(.intHour, "00") + ":" + Format(.intMinute, "00") _
    + ":" + Format(.intSecond, "00") + ":" + Format(.intMilliseconds,"000")
End With

End Function
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.accessmvp.comhttp://www.mvps.org/access

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.

Thank You your effort is much appreciated, could I ask one little
question though, how do I use it? Where do I load it?

 

[Cactus]

VBA can handle time to the millisecond but the native functions ignore
this.

Here's how to deal with milliseconds including revised versions of all
the native date/time functions of VBA:

'MS Access Can Handle Millisecond Time Values--Really'
(http://www.devx.com/dbzone/Article/39046/1954)

/gustav