Help with Age calculation… (97 SR-2)

Topic

New Reply

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

Reply | Quote

Replies

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"))

Reply | Quote

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

Reply | Quote

do you really need the iif part

Reply | Quote

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

Reply | Quote

let us see the code you wound up with

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote