Quick help sorting a flexgrid, and other quick question

[blkshirt]

Hello all, I'm new to this forum, hopefully somebody can help me out with a couple minor problems I have with my code.

I have data inputed into a flexgrid, and would like to sort it by YEAR in increasing order. Year is (frmStats.flexGrid.Col = 0).

The other problem I'm having is that when I try to get a batting average for players or their slugging percentage I get "0" the only time I don't get that is when the number of at bats is 0 then I display ".000", I check for division by zero to stop a run time exectution error. Below is the code you need to help.

Thank you for your help!

blkshirt

Option Explicit


 Type Demo_Record
    LahmanID As String * 9
    LastName As String * 20
    FirstName As String * 15
    Bats As String * 1
    Throws As String * 1
    BirthMonth As Byte
    BirthDay As Byte
    BirthYear As Integer
    RookieSeason As Integer
    FirstStatsRecord As Long
    StatsRecordPosition As Byte
End Type

Type Stats_In
    LahmanID As String * 9
    Year As Integer
    Team As String * 4
    League As String * 4
    Games As Integer
    AtBats As Integer
    Runs As Integer
    Hits As Integer
    Doubles As Integer
    Triples As Integer
    HomeRuns As Integer
    RBIs As Integer
    SacHits As Integer
    SacFlies As Integer
    Steals As Integer
    Caught As Integer
    Walks As Integer
    Intentional As Integer
    HitByPitch As Integer
    StrikeOuts As Integer
    NextStatsRecord As Long
    StatsRecordPosition As Byte
End Type

Type Stats_Block
    Stats(1 To 5) As Stats_In
End Type

Type Count_Em_Block
    NumStats As Long
    x As String * 276
End Type

Global K As String
Global i As Integer
Global J As Integer

Global DemoFileName As String
Global StatsFileName As String

Global DemoRecord As Demo_Record
Global StatsBlock As Stats_Block
Global StatsIn As Stats_In
Global CountBlock As Count_Em_Block
Global PD As Stats_Block

Global Year As Integer
Global Team As String
Global Leauge As String
Global Games As Integer
Global Runs As Integer
Global AtBats As Integer
Global Hits As Integer
Global Doubles As Integer
Global Triples As Integer
Global HomeRuns As Integer
Global RBIs As Integer
Global SacHits As Integer
Global SacFlies As Integer
Global Steals As Integer
Global Caught As Integer
Global Walks As Integer
Global Intentional As Integer
Global HitByPitch As Integer
Global StrikeOuts As Integer

Global CurrentPlayer As Integer
Global NumSeasons As Integer
Global NumPlayers As Integer
Global FullName As String

Global numRecord As Integer
Global ApproxRecords As Long
Global RecsRead As Long
Global StatsRead As Integer
Global StatsRecord As Integer

Global YearRow As Integer
Global TeamRow As Integer
Global LeaugeRow As Integer
Global GamesRow As Integer
Global ABRow As Integer
Global HitRow As Integer
Global DoubleRow As Integer
Global TripleRow As Integer
Global HRRow As Integer
Global RBIRow As Integer
Global SacHRow As Integer
Global SacFRow As Integer
Global StealsRow As Integer
Global WalksRow As Integer
Global HitByRow As Integer
Global KRow As Integer
Global BARow As Integer
Global SPRow As Integer
Global TBRow As Integer
Global SingleRow As Integer
Global BA As Integer
Global SP As Integer
Global TB As Integer
Global firstbase As Integer




'Function used to Calculate a Players Batting Average
Function BatAvg()
    
    If Val(AtBats) = 0 Then
        frmStats.lblBA.Caption = ".000"
    Else
        BatAvg = Round(Val(Hits) / Val(AtBats), 3)
    End If
    
End Function
'Function used to Calculate a Players Total Base Count (Used in Slugging Percentage)
Function TotalBases()
   TotalBases = Val(Singles) + Val(Doubles * 2) + Val(Triples * 3) + Val(HomeRuns * 4)
End Function
'Function used to Calculate a Players Slugging Percentage
Function SluggingPct()
    
    If Val(AtBats) = 0 Then
        frmStats.lblSP.Caption = ".000"
    Else
        SluggingPct = Round(Val(TotalBases) / Val(AtBats), 3)
    End If
    
End Function
'Function used to Calculate a Players Total of Singles as it is not given in the data file.
Function Singles()
    Singles = Val(Hits) - Val(Doubles) - Val(Triples) - Val(HomeRuns)
End Function
Public Function BinSearch(ByVal Wanted As String, ByVal Lo As Integer, ByVal Up As Integer) As Integer

'*****************************************************
'*                  Search Function                  *
'*****************************************************


    If Lo > Up Then
        BinSearch = False
        MsgBox ("Player Not Found")
    Else
        i = (Lo + Up) / 2
        
        If Left(frmStats.cbo1.List(i), Len(Wanted)) = Wanted Then
            BinSearch = True
            frmStats.hsbScroll.Value = i
        Else
            If Left(frmStats.cbo1.List(i), Len(Wanted)) > Wanted Then
                BinSearch = BinSearch(Wanted, Lo, i - 1)
            Else
                BinSearch = BinSearch(Wanted, i + 1, Up)
            End If
        End If
      End If
End Function

[blkshirt]

the second half of code....

Public Sub DisplayPlayer()
Dim Bdate As String
Dim x As Integer
Dim y As Integer

    

    
    Get #2, frmStats.cbo1.ItemData(frmStats.cbo1.ListIndex), DemoRecord
    
    With DemoRecord
        frmStats.lblName.Caption = Trim(.LastName) & ", " & Trim(.FirstName)
        Bdate = Trim(.BirthMonth) & "/" & Trim(.BirthDay) & "/" & Trim(.BirthYear)
            If IsDate(Bdate) Then
                frmStats.lblBornInfo.Caption = Bdate
            Else
                frmStats.lblBornInfo.Caption = "<na>"
            End If
        frmStats.lblRookieInfo.Caption = .RookieSeason
        frmStats.lblSeasonInfo.Caption = .RookieSeason + NumSeasons
            If frmStats.lblSeasonInfo.Caption > 2001 Then
                frmStats.lblSeasonInfo.Caption = 2001
            End If
        frmStats.lblBatInfo.Caption = .Bats
        frmStats.lblThrowInfo.Caption = .Throws
        
        
        x = .FirstStatsRecord
        y = .StatsRecordPosition
        
    End With
    
    NumSeasons = 0
    StatsRead = 0
    ClearStats          'Clears out Statistics before the loop
    YearRow = 1
    TeamRow = 1
    LeaugeRow = 1
    GamesRow = 1
    ABRow = 1
    HitRow = 1
    DoubleRow = 1
    TripleRow = 1
    HRRow = 1
    RBIRow = 1
    SacHRow = 1
    SacFRow = 1
    StealsRow = 1
    WalksRow = 1
    HitByRow = 1
    KRow = 1
    BARow = 1
    SPRow = 1
    TBRow = 1
    SingleRow = 1
    
    
    
    Do Until x = 0
        NumSeasons = NumSeasons + 1
            Get #3, x, StatsBlock
                With StatsBlock.Stats(y)
                    frmStats.flexGrid.Rows = NumSeasons + 1   'Writes Row to FlexGrid
                        'frmStats.flexGrid.Sort
                        frmStats.flexGrid.Row = YearRow
                        frmStats.flexGrid.Col = 0
                        frmStats.flexGrid.Text = .Year
                        frmStats.flexGrid.Row = TeamRow
                        frmStats.flexGrid.Col = 1
                        frmStats.flexGrid.Text = .Team
                        frmStats.flexGrid.Row = LeaugeRow
                        frmStats.flexGrid.Col = 2
                        frmStats.flexGrid.Text = .League
                    Games = Games + .Games
                        frmStats.flexGrid.Row = GamesRow
                        frmStats.flexGrid.Col = 3
                        frmStats.flexGrid.Text = .Games
                    AtBats = AtBats + .AtBats
                        frmStats.flexGrid.Row = ABRow
                        frmStats.flexGrid.Col = 4
                        frmStats.flexGrid.Text = .AtBats
                    Runs = Runs + .Runs
                    Hits = Hits + .Hits
                        frmStats.flexGrid.Row = HitRow
                        frmStats.flexGrid.Col = 5
                        frmStats.flexGrid.Text = .Hits
                    Doubles = Doubles + .Doubles
                        frmStats.flexGrid.Row = DoubleRow
                        frmStats.flexGrid.Col = 10
                        frmStats.flexGrid.Text = .Doubles
                    Triples = Triples + .Triples
                        frmStats.flexGrid.Row = TripleRow
                        frmStats.flexGrid.Col = 11
                        frmStats.flexGrid.Text = .Triples
                    HomeRuns = HomeRuns + .HomeRuns
                        frmStats.flexGrid.Row = HRRow
                        frmStats.flexGrid.Col = 12
                        frmStats.flexGrid.Text = .HomeRuns
                    RBIs = RBIs + .RBIs
                        frmStats.flexGrid.Row = RBIRow
                        frmStats.flexGrid.Col = 13
                        frmStats.flexGrid.Text = .RBIs
                    SacHits = SacHits + .SacHits
                        frmStats.flexGrid.Row = SacHRow
                        frmStats.flexGrid.Col = 14
                        frmStats.flexGrid.Text = .SacHits
                    SacFlies = SacFlies + .SacFlies
                        frmStats.flexGrid.Row = SacFRow
                        frmStats.flexGrid.Col = 15
                        frmStats.flexGrid.Text = .SacFlies
                    Steals = Steals + .Steals
                        frmStats.flexGrid.Row = StealsRow
                        frmStats.flexGrid.Col = 19
                        frmStats.flexGrid.Text = .Steals
                    Caught = Caught + .Caught
                    Walks = Walks + .Walks
                        frmStats.flexGrid.Row = WalksRow
                        frmStats.flexGrid.Col = 16
                        frmStats.flexGrid.Text = .Walks
                    Intentional = Intentional + .Intentional
                    HitByPitch = HitByPitch + .HitByPitch
                        frmStats.flexGrid.Row = HitByRow
                        frmStats.flexGrid.Col = 18
                        frmStats.flexGrid.Text = .HitByPitch
                    StrikeOuts = StrikeOuts + .StrikeOuts
                        frmStats.flexGrid.Row = KRow
                        frmStats.flexGrid.Col = 17
                        frmStats.flexGrid.Text = .StrikeOuts
                        
                        If Val(.AtBats) = 0 Then
                            frmStats.flexGrid.Row = BARow
                            frmStats.flexGrid.Col = 6
                            frmStats.flexGrid.Text = ".000"
                        Else
                            BA = Val(.Hits) / Val(.AtBats)
                            frmStats.flexGrid.Row = BARow
                            frmStats.flexGrid.Col = 6
                            frmStats.flexGrid.Text = Round(Val(BA), 3)
                        End If
                        
                        firstbase = Val(.Hits) - Val(.Doubles) - Val(.Triples) - Val(.HomeRuns)
                            frmStats.flexGrid.Row = SingleRow
                            frmStats.flexGrid.Col = 9
                            frmStats.flexGrid.Text = firstbase
                        TB = Val(firstbase) + Val(.Doubles * 2) + Val(.Triples * 3) + Val(.HomeRuns * 4)
                            frmStats.flexGrid.Row = TBRow
                            frmStats.flexGrid.Col = 8
                            frmStats.flexGrid.Text = TB
                        If Val(.AtBats) = 0 Then
                            frmStats.flexGrid.Row = SPRow
                            frmStats.flexGrid.Col = 7
                            frmStats.flexGrid.Text = ".000"
                        Else
                            SP = Round(Val(TB) / Val(.AtBats), 3)
                            frmStats.flexGrid.Row = SPRow
                            frmStats.flexGrid.Col = 7
                            frmStats.flexGrid.Text = Round(Val(SP), 3)
                        End If
                            
                    YearRow = YearRow + 1
                    TeamRow = TeamRow + 1
                    LeaugeRow = LeaugeRow + 1
                    GamesRow = GamesRow + 1
                    ABRow = ABRow + 1
                    HitRow = HitRow + 1
                    DoubleRow = DoubleRow + 1
                    TripleRow = TripleRow + 1
                    HRRow = HRRow + 1
                    RBIRow = RBIRow + 1
                    SacHRow = SacHRow + 1
                    SacFRow = SacFRow + 1
                    StealsRow = StealsRow + 1
                    WalksRow = WalksRow + 1
                    HitByRow = HitByRow + 1
                    KRow = KRow + 1
                    StatsRead = StatsRead + 1
                    BARow = BARow + 1
                    SPRow = SPRow + 1
                    TBRow = TBRow + 1
                    SingleRow = SingleRow + 1
                    x = .NextStatsRecord
                    y = .StatsRecordPosition
                End With
    Loop
           
    
    With frmStats
        
        .lblGP.Caption = Games
        .lblAB.Caption = AtBats
        .lblH.Caption = Hits
        .lblBA.Caption = BatAvg
        .lblSP.Caption = SluggingPct
        .lbl1B.Caption = Singles
        .lbl2B.Caption = Doubles
        .lbl3B.Caption = Triples
        .lblHR.Caption = HomeRuns
        .lblSH.Caption = SacHits
        .lblSF.Caption = SacFlies
        .lblW.Caption = Walks
        .lblK.Caption = StrikeOuts
        .lblHBP.Caption = HitByPitch
        .lblSB.Caption = Steals
        .lblRecord.Caption = FormatNumber(frmStats.hsbScroll.Value, 0) & "    of    " & NumPlayers
    End With
End Sub


Public Function ClearStats()
         Games = 0
         Runs = 0
         AtBats = 0
         Hits = 0
         Doubles = 0
         Triples = 0
         HomeRuns = 0
         RBIs = 0
         SacHits = 0
         SacFlies = 0
         Steals = 0
         Caught = 0
         Walks = 0
         Intentional = 0
         HitByPitch = 0
         StrikeOuts = 0
End Function

[James Stanich]

Too much code. Basically add a hidden column with a "String" version of the year. The sort on that column as such...

Me.MSFlexGrid1.Col = 6
        
Me.MSFlexGrid1.Sort = flexSortGenericDescending

[blkshirt]

Originally posted by James Stanich
Too much code. Basically add a hidden column with a "String" version of the year. The sort on that column as such...

Me.MSFlexGrid1.Col = 6
        
Me.MSFlexGrid1.Sort = flexSortGenericDescending

Thanks, I did get it to work...

blkshirt

[James Stanich]

Glad you got it working...