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

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Help with Age calculation... (97 SR-2)

do you really need the iif part

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

let us see the code you wound up with

8. ## 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. ## 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. ## 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
•