Non said:
Hi
Access 2000
I have a table with contenders of a running contest. Field names are name,
time1, time2, timeaverall
I sort the table on the timeoverall field.
Now I want to add 2 fields, containing each contenders position with time1
and time2
The position of the timeoverall I add with a text field in a report, so I
only want to add the Pos-fields for time1 and time2
Example:
Table:
Name time1 time2 timeoverall
aaa 01:15 12:13 13:28
bbb 01:14 13:01 14:15
ccc 02:18 10:10 12:28
...
The queryresult I want with the new Pos field
Name time1 Pos Time2 Pos Timeoverall
ccc 02:18 (3) 10:10 (1) 12:28
aaa 01:15 (2) 12:13 (2) 13:28
bbb 01:14 (1) 13:01 (3) 14:15
...
Who can/will help?
As John said, Date/Time data types aren't ideal for storing durations.
I also agree that TimeOverall is a separate calculation that should be
done in the query. I decided to try to solve this problem using some
time conversion functions.
tblRunnerTimes
RTID AutoNumber
RName Text
Time1 Date/Time Format: hh:nn (Note: This is really minutes and seconds
-- no 14 hour races!)
Time2 Date/Time Format: hh:nn
RTID RName Time1 Time2
1 ccc 2:18 10:10
2 aaa 1:15 12:13
3 bbb 1:14 1:01
4 ddd 1:14 1:02
'---Begin Module Code---
Public Function Seconds2DDHHNNSS(lngTotalSeconds As Long) As String
Dim intDays As Integer
Dim lngHours As Long
Dim intMinutes As Integer
Dim intSeconds As Integer
lngHours = lngTotalSeconds \ 3600
intDays = lngHours \ 24
lngHours = lngHours - intDays * 24
intMinutes = (lngTotalSeconds - intDays * 86400 - lngHours * 3600) \ 60
intSeconds = lngTotalSeconds - intDays * 86400 - lngHours * 3600 -
intMinutes * 60
Seconds2DDHHNNSS = Format(intDays, "00") & ":" & Format(lngHours, "00")
& ":" & Format(intMinutes, "00") & ":" & Format(intSeconds, "00")
End Function
Public Function HHNN2Seconds(dtTime As Date) As String
HHNN2Seconds = Hour(dtTime) * 60 + Minute(dtTime)
End Function
Public Function DDHHNNSS2Seconds(strTime As String) As Long
Dim lngDays As Integer
Dim intHours As Integer
Dim intMinutes As Integer
Dim intSeconds As Integer
Dim intColon As Integer
intColon = InStr(1, strTime, ":", vbTextCompare)
lngDays = CLng(Left(strTime, intColon - 1))
intHours = CInt(Mid(strTime, intColon + 1, 2))
intMinutes = CInt(Mid(strTime, intColon + 4, 2))
intSeconds = CInt(Right(strTime, 2))
DDHHNNSS2Seconds = intSeconds + 60 * CLng(intMinutes + 60 *
CLng(intHours + 24 * lngDays))
End Function
Public Function AddDDHHNNSS(strTime1 As String, strTime2 As String) As
String
AddDDHHNNSS = Seconds2DDHHNNSS(DDHHNNSS2Seconds(strTime1) +
DDHHNNSS2Seconds(strTime2))
End Function
Public Function SubtractDDHHNNSS2Seconds(strTime1 As String, strTime2 As
String) As Long
SubtractDDHHNNSS2Seconds = DDHHNNSS2Seconds(strTime1) -
DDHHNNSS2Seconds(strTime2)
End Function
'---End Module Code---
qryRunnerTimes:
SELECT RName, Seconds2DDHHNNSS(HHNN2Seconds(Time1)) AS FullTime1,
(SELECT Count(*) + 1 FROM tblRunnerTimes AS A WHERE DateDiff("s",
A.Time1, tblRunnerTimes.Time1) > 0) AS Rank1,
Seconds2DDHHNNSS(HHNN2Seconds(Time2)) AS FullTime2, (SELECT Count(*) + 1
FROM tblRunnerTimes AS A WHERE DateDiff("s", A.Time2,
tblRunnerTimes.Time2) > 0) AS Rank2, AddDDHHNNSS(FullTime1, FullTime2)
AS TimeOverall FROM tblRunnerTimes;
!qryRunnerTimes:
RName FullTime1 Rank1 FullTime2 Rank2 TimeOverall
ccc 00:00:02:18 4 00:00:10:10 3 00:00:12:28
aaa 00:00:01:15 3 00:00:12:13 4 00:00:13:28
bbb 00:00:01:14 1 00:00:01:01 1 00:00:02:15
ddd 00:00:01:14 1 00:00:01:02 2 00:00:02:16
Note: I tested the following expression from 1 to 10000000 (about 115
days of seconds):
lngI = DDHHNNSS2Seconds(Seconds2DDHHNNSS(lngI))
These functions overcome the following shortcomings:
1) You can use strings to store durations so that you aren't limited by
the Date/Time data type. I.e., you can use a Text field for Time1 and
Time2. Note: The SubtractDDHHNNSS2Seconds function can replace the
DateDiff function in the query when using strings for Time1 and Time2.
2) HHNN2Seconds can be widened so that you don't get a data type
mismatch when, say, you have 62 minutes. HHNN2Seconds can also be
adapted easily to take a string as input.
3) More than two durations can be added together.
If you know the two races won't be going over, say, an hour, you can
display just the right five characters of a FullTime string for Time1
and Time2.
James A. Fortune
(e-mail address removed)
