CD Tom,
Your table makes this more difficult, because it has these values in separate fields, rather than in rows, but I can understand why you might want to have it set up this way.
I would declare two public variables, one for the fastest time, another for the second fastest time.
I would declare a function that could be called from within your code which would compare the current values of those two best times, and update the global variables as necessary. You have to determine where the first two time values fall first. You just need to determine what you will do with the final value.
I won't give code for getting your table values into this loop, as you will probably either use a recordset, or draw directly from a form--your choice. Depends on how you are using this second best value.
This should work, and rather quickly, since it is just cycling through 18 values.
Let me know how it works.....
-
Option Compare Database
-
Option Explicit
-
-
Public dtmNumberOne As Date
-
Public dtmNumberTwo As Date
-
-
Private Sub FindTopTwo()
-
dtmNumberOne = Time1
-
If Time2 < dtmNumberOne Then
-
dtmNumberTwo = dtmNumberOne
-
dtmNumberOne = Time2
-
Else
-
If Time2 < dtmNumberTwo Then dtmNumberTwo = Time2
-
End If
-
EvaluateTime Time3
-
EvaluateTime Time4
-
EvaluateTime Time5
-
'Etc.....
-
Debug.Print dtmNumberTwo
-
End Sub
-
-
Private Function EvaluateTime(dtmTime As Date)
-
If dtmTime < dtmNumberOne Then
-
dtmNumberTwo = dtmNumberOne
-
dtmNumberOne = dtmTime
-
Else
-
If dtmTime < dtmNumberTwo Then dtmNumberTwo = dtmTime
-
End If
-
End Function
-
489
