Duane,
I spent all morning in the immediate window. I learned a lot.
I believe I found one issue with the original module. I have copied the whole code below. After further testing I realized I could never get a -1 to show up even with only one score recorded.
I believe the strSQL statement was always pulling two game even if one of the score fields was blank. I added AND gtePoints is Not Null to the strSQL and it seems to work now as I get a -1 if only one record has a score. I added a few Debug.print statements to help me see what is happening.
All of this looks good. I will send my problem on the second email.
Public Function IsWinner(lngFscID As Long, lngGteID As Long, Optional strGL As String = "G") As Integer
' lngFscID is the "game" and corresponds to the gteFscID (Facility Schedule) column of t_EventParticipants
' lngGteID is the primary key of t_EventParticipants
' strGL is a string value where "G" means the winner has the Greater/higher score
'Function should return:
' winner = 1
' loser = 2
' tie = 3
' NA = -1 only one result
Debug.Print lngFscID
Debug.Print lngGteID
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSort As String
Dim intFirstScore As Integer
Dim intSecondScore As Integer
Dim lngFirstGteID As Long
Dim lngSecondGteID As Long
If strGL = "G" Then
strSort = "DESC"
Else
strSort = ""
End If
Set db = CurrentDb
strSQL = "SELECT gteGteID,gtePoints FROM t_EventParticipants WHERE gteFscID = " & lngFscID & " AND gtePoints is Not Null ORDER BY gtePoints " & strSort
Set rs = db.OpenRecordset(strSQL)
'Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 1 Then 'more than one score recorded
rs.MoveFirst
lngFirstGteID = rs!gteGteID 'grab the first GteID which should be winner or tied
intFirstScore = rs!gtePoints 'grab the first score/result
rs.MoveNext
lngSecondGteID = rs!gteGteID
intSecondScore = rs!gtePoints 'grab the first score/result
Debug.Print lngFirstGteID & " 1st Gte"
Debug.Print intFirstScore & " 1st Score"
Debug.Print lngSecondGteID & " 2nd Gte"
Debug.Print intSecondScore & " 2nd Score"
Select Case True
Case intSecondScore = intFirstScore 'it's a tie
IsWinner = 3
Case lngGteID = lngFirstGteID 'the winning participant should be the first record
IsWinner = 1
Case Else
IsWinner = 2
End Select
Else
IsWinner = -1 'only one result so winner can't be determined
End If
rs.Close
End Function
Bill
Bill Singer
I always go back to using the immediate window and testing. You can add a breakpoint in your code to step through the lines one by one (with [F8]) and hovering over any variable to see its value.
Duane
