Results 1 to 3 of 3
  1. #1
    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.

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

    Any suggestions?

    Regards,
    Bronwyn

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

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

    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. #3
    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...

    =IIf(DateDiff("m",[DOB],Date())<168,(DateAdd("m",174,[DOB])),DateAdd("m",6,[StartDate]))

    Regards,
    Bronwyn

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •