Calculation Setup problem. Please Help.

[GMF]

Hi everyone, here is the problem at hand:

I am trying to create a database that keeps track of student information at a University. The goal is to have a field that can keep track of what "year" the student is in aoutomatically based on the academic year, not the calendar year. We have the following possible "year" designations in our program:

OD1: First year student

OD2: Second year student

OD3: 3rd Year student

OD4: Fourth Year student

E1: Extended Program 1st year

E2: Extended Program 2nd year

Here are the fields I have in my table:

Class Year: The user enters the year of anticipated graduation

Current Year: The Year designations explained above

Repeater: Yes/ No field indicating if they have failed

Extended: designates if they are in the extended program (E1 or E2)

True class year: calculates new year of graduation if Repeater = Yes (just adds 1 to the class year)

Now, I have the following calculation field to keep track of the Current year:

Case ( TrueYear = Year ( Get ( CurrentDate ) ) + 1; "OD4"; TrueYear = Year ( Get ( CurrentDate ) ) + 4 and Extended = "No"; "OD1"; TrueYear = Year ( Get ( CurrentDate ) ) + 4 and Extended = "Yes"; "E2";TrueYear = Year ( Get ( CurrentDate ) ) + 3; "OD2";TrueYear = Year ( Get ( CurrentDate ) ) + 2; "OD3"; TrueYear = Year ( Get ( CurrentDate ) ) + 5; "E1" )

Basically I am using Case to check and see how far away the student is from their graduation date based on the current calendar year, but I really need to base it on the academic year.

I'm sure there are issues here but I just want to show you all the road I was heading down. I does not work for all cases.

How can I accomplish what I want to do and how can I change the year specification after the academic year and not the calendar year (for example: An OD1 changes to OD2 after August 1st and so on)? Is there a more efficient way rather than using Case?

i hope this is not too confusing.

Thanks in advance.

[comment]

I am not sure I follow your entire logic (what happens if someone fails twice?), but adjusting the year to start on a date other than Jan 1 is rather simple:

Year ( Get ( CurrentDate ) ) + ( Get ( CurrentDate ) < Date ( x ; y ; Year ( Get ( CurrentDate ) ) ) )

where x is the month and y is the day of the turning. Change the plus to a minus and the > to a < according to your preference.