Re: [MSAccessProfessionals] Winning team auto calculate

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

 

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Thursday, April 1, 2021 4:41 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Winning team auto calculate

 

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