I needed a calc field that determines a student's age in yrs. & months as of December 1 of the current school year. I managed it in 3 fields:

half yr = (MiddleWords(cur sch yr, 1, 1)) followed by

dec.1 = Date(12, 1, half yr) followed by

age 12.1 = NumToText(Year(dec 1) - Year(dob) - Case(dec 1< Date(Month(dob), Day(dob), Year(dec 1)), 1, 0)) & "/ " & NumToText(Mod(Month(dec 1) - Month(dob) + 12 - Case(Day(dec 1) < Day(dob), 1, 0), 12)).

My problem is, the "age 12.1" calc field currently yields an entry of 2000/0 as soon as I enter data in the current school year field. It registers correctly as soon as the student's dob is entered but the appearance of nonsense numbers confuses users. Checking "do not evaluate if all referenced fields are empty" doesn't help with this. Is there a way to force the final calculation to wait until ALL referenced fields contain data?

quote:

---

Originally posted by tmac:

I needed a calc field that determines a student's age in yrs. & months as of December 1 of the current school year. I managed it in 3 fields:

half yr = (MiddleWords(cur sch yr, 1, 1)) followed by

dec.1 = Date(12, 1, half yr) followed by

age 12.1 = NumToText(Year(dec 1) - Year(dob) - Case(dec 1< Date(Month(dob), Day(dob), Year(dec 1)), 1, 0)) & "/ " & NumToText(Mod(Month(dec 1) - Month(dob) + 12 - Case(Day(dec 1) < Day(dob), 1, 0), 12)).

My problem is, the "age 12.1" calc field currently yields an entry of 2000/0 as soon as I enter data in the current school year field. It registers correctly as soon as the student's dob is entered but the appearance of nonsense numbers confuses users. Checking "do not evaluate if all referenced fields are empty" doesn't help with this. Is there a way to force the final calculation to wait until ALL referenced fields contain data?

---

Try this:

age 12.1 = Case( not IsEmpty( dob ), NumToText(Year(dec 1) - Year(dob) - Case(dec 1< Date(Month(dob), Day(dob), Year(dec 1)), 1, 0)) & "/ " & NumToText(Mod(Month(dec 1) - Month(dob) + 12 - Case(Day(dec 1) < Day(dob), 1, 0), 12)), "" )

In other words, check that the dob field is not empty, and if it is, set the age field to an empty string.

Chuck

It worked. Thanks!