Delete combo box field automatically next year 1st may 2022

[Welshgasman]

Just have only the one test for 12 and add a date check to your function for Class 6, then it will always return Grade 6 after that date.

[erickemboi]

kindly, please somebody to help with a function to delete CLASS 6 on 1ST MAY 2022. I am stuck. Please help. Update this dummy students database

[Welshgasman]

Function fails if school year not supplied, so hardly optional?

Change your computer date to 1st May 2022 to test new grade.
Test for today as well

Function ConvertBirthdayToGrade(Birthday As Date, Optional SchoolYear As Variant) As String
    Dim intGrade As Integer
    On Error Resume Next
    '5 YEARS -PP1
    '6 YEARS -PP2
    '7 YEARS- GRADE 1
    '8 YEARS- GRADE 2
    '9 YEARS- GRADE 3
    '10 YEARS- GRADE 4
    '11 YEARS- GRADE 5
    '12 YEARS- CLASS 6(TO BE DELETED permanently ON 1ST MAY 2022)
    '12 YEARS- GRADE 6
    '13 YEARS- CLASS 7
    '14 YEARS- CLASS 8

    'If IsEmpty(SchoolYear) Then SchoolYear = Format(Date, "yyyy")

    intGrade = CInt(Left(SchoolYear, 4)) - Year(Birthday)

    Select Case intGrade
    Case Is < 5
        ConvertBirthdayToGrade = "NOT YET IN SCHOOL! LEARNER STILL VERY YOUNG!"
    Case 5
        ConvertBirthdayToGrade = "PP1"
    Case 6
        ConvertBirthdayToGrade = "PP2"
    Case 7
        ConvertBirthdayToGrade = "GRADE 1"
    Case 8
        ConvertBirthdayToGrade = "GRADE 2"
    Case 9
        ConvertBirthdayToGrade = "GRADE 3"
    Case 10
        ConvertBirthdayToGrade = "GRADE 4"
    Case 11
        ConvertBirthdayToGrade = "GRADE 5"
    Case 12
        If Date < #5/1/2022# Then
            ConvertBirthdayToGrade = "CLASS 6"
        Else
            ConvertBirthdayToGrade = "GRADE 6"
        End If
    Case 13
        ConvertBirthdayToGrade = "CLASS 7"
    Case 14
        ConvertBirthdayToGrade = "CLASS 8"
    Case Is >= 15
        ConvertBirthdayToGrade = "LEARNER NO LONGER IN SCHOOL! CHECK BIRTH DATE!!"
    End Select


End Function

[erickemboi]

You are genius. Thanks so much. God bless you. Cheers!

[moke123]

Still not sure I follow you. your going to need to be more specific

Deleted where? In the combobox? In the table?

Do you want case 12 to show class6 untill 5/1/22 and then it will show Grade6 from then on?
if thats the case you could use

Case 12
ConvertBirthdayToGrade = IIf(Date < #5/1/2022#, "Class6", "Grade6")

[moke123]

You appear to have your test for the missing argument commented out, so no default value is being assigned as Gasman points out.

Rather than IsEmpty, I believe you want IsMissing.

Also dont think you need all the formating as the year function should suffice.

Function ConvertBirthdayToGrade(Birthday As Date, Optional SchoolYear As Variant) As String
    Dim intGrade As Integer
    On Error Resume Next


    If IsMissing(SchoolYear) Then SchoolYear = Date


    intGrade = Year(SchoolYear) - Year(Birthday)


    Select Case intGrade


    Case Is < 5
        ConvertBirthdayToGrade = "NOT YET IN SCHOOL! LEARNER STILL VERY YOUNG!"
    Case 5
        ConvertBirthdayToGrade = "PP1"
    Case 6
        ConvertBirthdayToGrade = "PP2"
    Case 7
        ConvertBirthdayToGrade = "GRADE 1"
    Case 8
        ConvertBirthdayToGrade = "GRADE 2"
    Case 9
        ConvertBirthdayToGrade = "GRADE 3"
    Case 10
        ConvertBirthdayToGrade = "GRADE 4"
    Case 11
        ConvertBirthdayToGrade = "GRADE 5"
    Case 12
        ConvertBirthdayToGrade = IIf(Date < #5/1/2022#, "Class6", "Grade6")
    Case 13
        ConvertBirthdayToGrade = "CLASS 7"
    Case 14
        ConvertBirthdayToGrade = "CLASS 8"
    Case Is >= 15
        ConvertBirthdayToGrade = "LEARNER NO LONGER IN SCHOOL! CHECK BIRTH DATE!!"
    End Select


End Function

[erickemboi]

Perfect. Thank you so much. God bless you. Once again, thank you.👏👏👏👏👏👏