# Thread: Calculate date depending on criteria

1. Registered User
Join Date
May 2006
Posts
25

## Unanswered: Calculate date depending on criteria

Hello,

I have a database the keeps a record of Participants in our Award Program. I am creating a report, and in it, I have a text box that needs to display the completion date of each participant. Now, that date may vary depending on the age of the participant.

For example: If the participant is younger than 14 years of age, the completion date would be 6 months from their 14th birthday. If the participant is 14 or over, then the completion date would be 6 months from the date they started the program.

This is what I have attempted as an expression, but as you may gather, I know diddly squat about this.

Any suggestions?

Regards,
Bronwyn

2. Registered User
Join Date
Jul 2004
Location
Southampton, UK
Posts
368
Try (note the bracket has moved):

Note that "yyyy" returns 1 for a date difference of 1 day up to 1 year. So I think you need:

=IIf(DateDiff("yyyy", [DOB], Date()) - 1 > 14,(DateAdd("m", 6, [DOB]), DateAdd("m", 6, [StartDate]))

hth
Chris

3. Registered User
Join Date
May 2006
Posts
25
Thanks for your help Chris. You set me on the right track. I realised that I needed to calculate things by months instead of years, because often the difference between a participant's start date and their 14th birthday was just a matter of a few months. So here is what I ended up with - and it seems to work...