Date Issue

[EPOS]

I have a program which works out the persons age and then assigns them a "Squad number". I have got it working if the year is a normal year but the squad numbers year works from the 1st Sept to the 31 Aug the following year.. (Basically a UK schooling year)

I cant see how to convert this code to work using the UK schooling year... Any ideas?

Private Sub List1_Click(Index As Integer)

'Build the criteria to search for.
sql = "SELECT * FROM [Account Master] WHERE [Name] = " & """" & List1(Index).Text & """"

Set rs = db.OpenRecordset(sql, dbOpenDynaset)
If Not rs.EOF Then
    'Record found
    
    LblAge1(Index).Caption = DateDiff("yyyy", rs!DateofBirth, Now()) + Int(Format(Now(), "mmdd") < Format(rs!DateofBirth, "mmdd"))

    If LblAge1(Index).Caption > 18 Then
        MsgBox "This person is over 18 and unable to bowl in YBC tournaments"
        Lblsquad(Index).Caption = "?"
    End If
    If LblAge1(Index).Caption = 18 Or LblAge1(Index).Caption = 17 Then
        Lblsquad(Index).Caption = "A"
     End If
      If LblAge1(Index).Caption = 16 Or LblAge1(Index).Caption = 15 Then
        Lblsquad(Index).Caption = "S"
    End If
      If LblAge1(Index).Caption = 14 Or LblAge1(Index).Caption = 13 Then
        Lblsquad(Index).Caption = "J"
     End If
      If LblAge1(Index).Caption = 12 Or LblAge1(Index).Caption = 11 Then
        Lblsquad(Index).Caption = "B"
    End If
      If LblAge1(Index).Caption <= 10 Then
        Lblsquad(Index).Caption = "P"
      End If
     
    End If
End Sub

 

[kav123]

My guess is you would have set the beginning of a schooling year as a benchmark date, to compare the age of the person, i.e. say if by the beginning of the next term if a person is of this age, then you put him in this category. Hence, declare a date variable begDate, which would be the date in september for that particular year, and compare the DOB of the person with this date in september for that year. And based on the difference put him in whichever squad.

What i am trying to say is, you will have to consider the month as well, along with the year. And keep the date of beginning of the term as a comparing date.

Not sure, but could give it a try.