calculate students current grade based on Birthday

[ralphyehle]

Hi,

I want to calculate student's current grade levels based on their birthday.

If todays date is 04/11/2008 then calculate the most recent past September 1st as 09/01/2007, use that September date minus the students birthday, to find age as a whole number and from that, calculate the grade levels, If 5 years old then Kindergarten, if 6 then first grade, etc. all the way up to grade 12.

I hope this is clear?

Ralph

 

[Rabbie]

Hi,

I want to calculate student's current grade levels based on their birthday.

If todays date is 04/11/2008 then calculate the most recent past September 1st as 09/01/2007, use that September date minus the students birthday, to find age as a whole number and from that, calculate the grade levels, If 5 years old then Kindergarten, if 6 then first grade, etc. all the way up to grade 12.

I hope this is clear?

Ralph

Aircode for your problem would be to use the Case statement

Calculate Age
Select Case Age
case 5
Grade = "Kindergarten"
Case 6
Grade = "1st Grade"

and so on

 

[Deleted member 30250]

Can't I find anything better to do on a Saturday afternoon? This formula worked for me in Excel, it would need 'translating' into Access:

=YEAR(IF(DATE(YEAR(AsOfDate),9,1)>AsOfDate,DATE(YEAR(AsOfDate)-1,9,1),DATE(YEAR(AsOfDate),9,1)))-YEAR(DOB)+IF(MONTH(DOB)>MONTH(IF(DATE(YEAR(AsOfDate),9,1)>AsOfDate,DATE(YEAR(AsOfDate)-1,9,1),DATE(YEAR(AsOfDate),9,1))),1,0)-5

Where AsOfDate could be today, DOB is date of birth. (Kindergarten comes out as zero).

 

[missinglinq]

Here's code I placed in the AfterUpdate event for a control named DOB. It uses a formula that calculates the exact age on a given date, taking into account whether or not the birthday for that year has occurred yet.

Private Sub DOB_AfterUpdate()
Dim StartDate As Date
Dim Age As Integer

'Determine Start Date for Current School Year
If DateSerial(Year(Date), 9, 1) > Date Then
 StartDate = DateSerial(Year(Date) - 1, 9, 1)
Else
 StartDate = DateSerial(Year(Date), 9, 1)
End If

'Determine Actual Age at Start of Current School Year
Age = DateDiff("yyyy", [DOB], StartDate) - IIf(Format$(StartDate, "mmdd") < Format$([DOB], "mmdd"), 1, 0)

'Determine Grade Based on Actual Age and Assign to Grade Field
Select Case Age
 Case 5
   Me.Grade = "Kindergarden"
 Case 6
   Me.Grade = "First Grade"
 Case 7
   Me.Grade = "Second Grade"
 Case 8
   Me.Grade = "Third Grade"
 Case 9
   Me.Grade = "Fourth Grade"
 Case 10
   Me.Grade = "Fifth Grade"
 Case 11
   Me.Grade = "Sixth Grade"
 Case 12
   Me.Grade = "Seventh Grade"
 Case 13
   Me.Grade = "Eighth Grade"
 Case 14
   Me.Grade = "Ninth Grade"
 Case 15
   Me.Grade = "Tenth Grade"
 Case 16
   Me.Grade = "Eleventh Grade"
 Case 17
   Me.Grade = "Twelfth Grade"
 Case Else
   Me.Grade = "Outside of School Age"
 End Select

End Sub

 

[ralphyehle]

Thanks much to both suggestions. I've got it working now.

Ralph

 

[colincliff]

Hi ralphyehle,

i'm looking for the answer to this too! What expression did you use in the end?

thanks in advance