Year of Graduation Field

[Zippyfrog]

Hi - I have a database I am working on where I am tracking our current students. There is a field in my table of student info called YearOfGraduation, which is 2008, 2009, 2010, etc. On the form where the user adds students, there is a calculated field called "Current Grade." I would like to accomplish two things.

1) How would I setup that calculated field so that it would say if the Year Of Graduation Field is 2008, then the Current Grade would be 12, Year of Graduation is 2009, it would say 11, and so forth? (Then once the graduate, it would just say "Graduate")

2) How would I setup the coding so that it the database checks the system date, and once the system date is July 1, then the kids get "moved up a year", in another words, on June 30, 2008, the class of 2008 are still listed as 12, but on July 1, 2008, the class of 2008 is listed a graduated and the class of 2009 are now 12.

I have a feeling that this is a rather simple if/then statement calling the system date, but I am not sure how to do that because I am only familiar with date().

 

[KenHigg]

1. I would write a simple function to return this on the fly.
2. I would recommend that this be an explicit task (push button to roll students up a class), that a user is instructed to do one a year rather than have the database execute the routine if a condition exists...

 

[Zippyfrog]

Could you elaborate on how I would begin to setup that rule? I just can't wrap my mind around how to handle it because if I base the current year off of the year of graduation, for part of 2008, the class of 2008 would be equal to 12, but then the fall of 2008 has the class of 2009 equaling grade 12. I am not quite sure how to even begin to write code for this scenario