A difficult if then statement

[Renny Willins]

I need to create a calculation that will allow me to add 5, 2 or 1 years to a review date... but there is a snag.

In a nutshell here is what is required... I will get to the snag at the end.

A person in this database has a medical category (1,2 or 3) If they are category 1 or 2 and they are under 50 then they must be medically checked a 5 year intervals. If they are between 50 and 60 it must be every 2 years. If they are category 3 after the age of 40 it must be every 5 years.

That in its self is causing me some grief but here is the snag.

The field I am creating adds years to a review date OK but if a person has a medical at 49 on the 1/1/08 they will not need to be reviewed until 1/1/2013 and my date of review field will do this no worries. BUT, when he turns 50 the date of review field will change and rob him of three years.

How can I stop this from happening!!!:)

[mr_vodka]

Either use a stored calc or use a number field with calculated result with Do not replace unchecked.

[Fitch]

In addition to John's advice: Your date of review field needs to calculate not on the person's actual age, but their age at the date of their last review. If they haven't had a review, you can use their actual age. Is that enough to get you started?