Formatting Race Times

[Mandy J.S.]

I have a database of runners and walkers from my race. Race times are done
in hours, minutes and seconds. How can I get it to not format it as an
actual time? I put in 23:42 and it puts it in a 12:23:42 AM. I want it to
just read 23:42

I would also love for it to automatically figure out the pace. So, if I
have a 5 mile race then I can have it figure out that the 23:42 race time
equals a 4:45 pace.

Then I need for it to pick out the fastest time for male runner overall,
then masters and then age groups.

This is a race that I have to raise money for the community, so I don't have
the funds to have this done or buy a fancy race program. Thanks for any
assistance.

 

[scubadiver]

In Access, this is always a point in time NOT a duration of time. The
following is a copy of a response from this board. It was so useful I kept it
for reference:



People can write the following on a piece of paper...

7:45

.....and it can mean either the point on the clock (seven forty-five) or it
can
mean an amount of time (seven hours and forty-five minutes). In Access
however, this ALWAYS means the point in time (seven forty-five) and never a
duration.

Abstractly, subtracting one DateTime from another is a meaningless excercise.
What is 8 O'Clock minus 4 O'Clock? What is Thursday divided by Friday? The
reason that this can be done in Access to some degree is because Access
stores
dates under the covers as numbers based on a reference DateTime of Midnight
on
12/30/1899. This is a convenience and solves many date manipulation
problems,
but one must keep in mind that "date math" is only possible because of how
Access has chosen to store dates and that there are some limitations.

In Access 1:00 + 2:00 will yield 3:00. What that expression actually does
though is add the numeric representations of...

12/30/1899 01:00:00 + 12/30/1899 02:00:00

.....and the result is the number that represents 12/30/1899 03:00:00. Great
right? However; if the math results in a different DAY and your expression
only
displays or concerns itself with the TIME then this all starts to break down.

23:00:00 + 3:00:00 = 2:00:00

This is because the 2 am result is actually on 12/31/1899 instead of on
12/30/1899. In your case you are subtracting two DateTimes and the result is
going to be a DateTime. There is no such thing as a "negative" DateTime. If
you add two dates in this manner you get a date. If you subtract two dates
you
get a date.

For this reason it is not generally recommended to do "date math" with simple
arithmetic expressions. It is better to use DateDiff() to determine how many
(days, hours, seconds, etc.), there are between the operands and then
converting
that result back into an expression that looks like a duration like hh:nn.

Since DateDiff gives you an integer response indicating how many "intervals"
there are between the two dates then it will return a negative value if the
second date is earlier and a positive value if the second date is later.
Straight arithmetic on two dates will never do this.

 

[Dale Fye]

Mandy,

As scuba diver said, storing durations as a date/time value does not work
very well.
You might want to consider storing these start and end times as strings,
then using the CDATE function to convert them to the date data type before
doing your math.

To format the output, use the Format function to format your times. As an
example

Format(Cdate(EndTime) - cdate(StartTime), "hh:mm:ss")

For the pace,

format((cdate(EndTime) - cdate(StartTime))/Distance, "hh:mm:ss")

HTH
Dale

 

[George Nicholson]

?format("12:23:42 AM","nn:ss")
returns 23:42

?format("12:23:42 AM","hh:nn:ss")
returns 00:23:42

Either use the format function as shown above, or apply the desired format
specification ("nn:ss") to the Format property of text box controls, etc.

As for data entry, I think you'll simply need to input the leading 0: for
hours. Either that or add some code that would "translate" any hours input
to minutes and minutes to seconds:

'(Air code)
Public Function RestateHoursAsMinutes(dtmInput as Date)as Date
Dim iHours as integer
Dim iMinutes as Integer

iHours = Hour(dtmInput)
If iHours>0 then
iMinutes = Minute(myInput)
dtmInput = TimeSerial(0, iHours, iMinutes)
End If
RestateHoursAsMinutes = dtmInput
End Function
If times are entered with leading 0 hour, they would flow through this with
no change. If no leading 0 hour, then "hour" will be converted to minute and
minutes converted to seconds. As written, this fails for times less than 1
minute. Those should be entered as 0:0:xx.

HTH,

 

[Bob Quintal]

I have a database of runners and walkers from my race. Race times
are done in hours, minutes and seconds. How can I get it to not
format it as an actual time? I put in 23:42 and it puts it in a
12:23:42 AM. I want it to just read 23:42

I would also love for it to automatically figure out the pace.
So, if I have a 5 mile race then I can have it figure out that the
23:42 race time equals a 4:45 pace.

Then I need for it to pick out the fastest time for male runner
overall, then masters and then age groups.

This is a race that I have to raise money for the community, so I
don't have the funds to have this done or buy a fancy race
program. Thanks for any assistance.

You are trying to store a duration in a time field. The two are not
the same, although both use minutes and seconds.

There are several methods to store durations. One is to store each
duration in a string field. The other is to store the number of
seconds in a numeric field. A third is to store the start time and
the end time by putting a button that stores noe() into each field.

Once the data is in a field or fields, you need some custom
functions to manipulate that data: if you store as text, you must
get the minutes, skip the colon, and get the seconds. then you
multiply the minutes by 60 and add the seconds. If you want to store
the number, you type the interval into an unbound field, and use the
same algorithm to convert to seconds before writing them to the
table.

you can then do all your calcs and sorting base on the number of
seconds.

When you need to show the durations, you use a reverse of the
process, and I have published a user-defined function in the group
comp.databases.ms-Access in 2005 that does exactly that. Do a Google
Groups search for Bob Quintal Sec2Dur() and it will show up.