Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with Age calculation... (97 SR-2)

    I'm trying to calculate the age from date of birth. In the query design grid I have this
    AGE: IIf([DOB]>Date(),DateDiff("yyyy",[DOB],Date())-1,DateDiff("yyyy",[DOB],Date()))

    I know my problem is in the expression if the IIF statement. But how do I compare month and day from DOB to month and day from Date() (todays date). Anyone following me here?? Thanks to all in advance.
    Jols

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Age calculation... (97 SR-2)

    i've never tried it in a query but in a form i use this code
    Textbox = DateDiff("yyyy", [BirthDate], Now()) + Int(Format(Now(), "mmdd") < Format([BirthDate], "mmdd"))

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Age calculation... (97 SR-2)

    Hey Jerry,
    I'm getting "invalid syntax" error and the insertion point after the error is going to the semi colon in the first datediff statement. Here is what I have now useing what you sent.
    AGE: IIf(DateDiff("yyyy", [DOB], Now()) + Int(Format(Now(), "mmdd") < Format([DOB], "mmdd")),DateDiff("yyyy",[DOB],Date())-1,DateDiff("yyyy",[DOB],Date()))

    I'm getting really confused now with all the datediff's!!!!
    I don't really understand how the first part that you sent works. Or if it would even evaluate to true of false which it would have to do for the IIf statement to work. Right?? Thanks Jerry

  4. #4
    rverwij
    Guest

    Re: Help with Age calculation... (97 SR-2)

    Works fine in a query too!
    Age: DateDiff("yyyy",[BirthDate],Now())+Int(Format(Now(),"mmdd")<Format([BirthDate],"mmdd"))

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Age calculation... (97 SR-2)

    I see what I did wrong. Sorry
    I'm not getting the invalid syntax anymore but doesn't seem that calculations are correct. I have persons date of birth as 1/30/74 and it's calculating his age as 26 when it should be 27. Any ideas??
    Thanks

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Age calculation... (97 SR-2)

    do you really need the iif part

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Age calculation... (97 SR-2)

    let us see the code you wound up with

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Age calculation... (97 SR-2)

    I was getting syntax error because when I copied it from my email client it converted the less than symbol to alt;. I just failed to notice it.

    AGE: IIf(DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")>Format([DOB],"mmdd")),DateDiff("yyyy",[DOB],Date()-1),DateDiff("yyyy",[DOB],Date()))

    What do you think?
    Thanks

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Age calculation... (97 SR-2)

    have you tried it without the iif part and without the second datediff part or the third datediff part
    if i understand what you want it isn't needed

  10. #10
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Age calculation... (97 SR-2)

    Hey Jerry what you suggested worked by removing the iif, and datediffs. Thanks alot!!!! Although I don't really understand how?? It's been a VERY long day. Thanks again for the help.
    Jols

Posting Permissions

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