expression to calculate childs school year in an access form

[colincliff]

Hi. I'm not an access expert by any means, but i have some experiance at building basic databases. I am trying to make a basic pupil registration/attendance database in the U.K.

I need an expression that will calculate a childs school year (grade) based on their date of birth.

In England, the cut-off date for school entry is 1 September; children must start school in the academic year during which they will become 5 years old. My expression needs to take this into account when calculating their school year.

For example a child born 31st August 1996 will be in school year 8, but a child born 1st Sept 1996 will be school year 7.

I hope this makes sense!

i have tried to use the expression below, however this doesnt seem to take into account whether the dob occurs before or after 1st sept.

=DateDiff("yyyy",[hck_dob],DateSerial(Year(Date())+(Format(Date(),"ddmm")<"0109"),1,9))-5

any ideas would be appreciated!

 

[colincliff]

i think i have worked out a way to do this incase anyone else needs it.

the following expression seems to work for me:
=(Year(Date())-iif(Month([hck_dob])>8,Year([hck_dob])+6,(Year([hck_dob])+5)))

 

[petercornwallsballs]

if you ever come back here colin, i wanted to say thanks for your post. it's always nice when someone figures out their own answer and then takes the trouble to come back and post it for others to make use of.

 

[Dandandan]

Hi

For those of you who stumbled here when trying to find the same answer for Mac Filemaker Pro.. this is the expression I used in Filemaker Pro for the same effect:

= Year ( Get(CurrentDate) ) - If ( Birth Month>8 ; Year(DOB)+6 ; Year(DOB)+5 )

Thanks to the original poster - as I wouldn't have got there alone!

 

[ontopofmalvern]

Hi
been using the version colincliff posted for a few months and all was going well - but it only works for part of year (Jan to August), this is because the amount you need to add onto difference betwen DOB year and current changes - on colin's version you get a different school year on 31st Dec than a day later on 1st Jan (Dec value is 1 too small). My rather in-elegant soltion is -

=(Year(Date())-IIf(Month([DOB])>8,Year([DOB])+6,(Year([DOB])+5)))+IIf(Month(Date())>8,1,0)

The second IIF statement adds a correction of 1 when system date is Sept to Dec.