Calculate current school grade level

I want to create a field on a form that will look at the child's birthdate
and calculate their school year (kindergarten through 12) based on their age
as of September of the current year. Example, today is April 11, 2008, I
would like the grade to be as of September 1, 2007.

Something like: If today is (whatever the date) then September 1 of the
prior year, minus the students birthdate = an integer rounded down, then 5 =
Kindergarten, 6 = first grade, 7 = second grade, etc., up to 12th grade.

Thank you in advance for any help.

Ralph

Hello Ralph.

Ralph wrote:
I want to create a field on a form that will look at the child's
birthdate and calculate their school year (kindergarten through 12)
based on their age as of September of the current year. Example,
today is April 11, 2008, I would like the grade to be as of
September 1, 2007.

Something like: If today is (whatever the date) then September 1
of the prior year, minus the students birthdate = an integer
rounded down, then 5 = Kindergarten, 6 = first grade, 7 = second
grade, etc., up to 12th grade.

You could use a function like this (in a standard module):

Option Compare Database
Option Explicit

Function SchoolYear(Birthdate As Date) As String

Dim September1st As Date, Today As Date
Dim Birthday As Date
Dim AgeOnSeptember1st As Integer

Today = Date
September1st = DateSerial(Year(Today), 9, 1)
If September1st Today Then
September1st = DateAdd("yyyy", -1, September1st)
End If
AgeOnSeptember1st = DateDiff("yyyy", Birthdate, September1st)
Birthday = DateAdd("yyyy", AgeOnSeptember1st, Birthdate)
If Birthday September1st Then
AgeOnSeptember1st = AgeOnSeptember1st - 1
End If
Select Case AgeOnSeptember1st
Case Is 5
SchoolYear = "too young for school"
Case 5
SchoolYear = "Kindergarten"
Case 6
SchoolYear = "first grade"
Case 7
SchoolYear = "second grade"
Case 8
SchoolYear = "third grade"
Case 9 To 17
SchoolYear = CStr(AgeOnSeptember1st - 5) & "th grade"
Case Else
SchoolYear = "too old for school"
End Select

End Function

--
Regards,
Wolfgang

You could use an expression similar to this as the Control Source for a
textbox on the form (I'm using generic name for BirthDate field, which I
assume is in the form's Record Source query):

=DateDiff("yyyy",[BirthDate],DateSerial(Year(Date())+(Format(Date(),"mmdd")"0 901"),9,1))-5

You will get a 0 from the above expression for Kindergarten grade. If you
want to show Kindergarten as the grade in that situation, use the text box's
Format property:
;;"Kindergarten"

--

Ken Snell
MS ACCESS MVP


"Ralph" wrote in message
news
I want to create a field on a form that will look at the child's birthdate
and calculate their school year (kindergarten through 12) based on their
age
as of September of the current year. Example, today is April 11, 2008, I
would like the grade to be as of September 1, 2007.

Something like: If today is (whatever the date) then September 1 of the
prior year, minus the students birthdate = an integer rounded down, then 5
=
Kindergarten, 6 = first grade, 7 = second grade, etc., up to 12th grade.

Thank you in advance for any help.

Ralph