Calculating Ages

[kymwilkinson]

I am new at Access. We are using Access 2013. Our database contains client information including DOB. We are required to report various numbers as related to specific "groups". One of them is "Elderly" and that group is defined as anyone over the age of 60. The following expression was created within a query that also reports the number of Youth, Female, etc. (I did not write the expression):



Elderly: IIf([ClientDOB] Is Null,0,IIf((DateDiff("yyyy",[ClientDOB],Date())>60),1,0))

The problem is that it is not calculating to the birthdate. So that if someone has a birthdate of 5/25/1955, they turned 60 yesterday, but it does not count them as "Elderly". It doesn't appear to me that it will count them until the end of the year.

I am having the same issue with the "Youth" expression, which is supposed to define anyone under 18:

Youth: IIf([ClientDOB] Is Null,0,IIf((DateDiff("yyyy",[ClientDOB],Date())<18),1,0))

Any suggestions for this newbie?
Thanks!

[June7]

They are 60, not 'over 60'. If you want to count fractions of year, that gets complicated.

Review https://www.accessforums.net/access/...day-15274.html

[kymwilkinson]

I don't know what happened to my previous replies, so I will just sum them up here. I had tried the link June7 provided and had errors in the syntax. I just found another piece of code in another forum that made me think if I changed the "yyyy" to "d" to represent the number of DAYS they were, that while not perfect in all situations, it was much more accurate than asking for a calculation on years. So the new expression is Elderly: IIf([ClientDOB] Is Null,0,IIf((DateDiff("d",[ClientDOB],Date())>21915),1,0))
This would be the number of days old (60 x 365.25).
And then Youth: IIf([ClientDOB] Is Null,0,IIf((DateDiff("yyyy",[ClientDOB],Date())<6574),1,0))
Number of days old (18 x 365.25). I did not use the decimal (6574.50 days) because I was afraid of another syntax error.

The new RUN revealed all calculations are correct. This should greatly diminish the number of errors we we're receiving.

Now to figure out how to "SOLVE" this issue on the forum!

[June7]

Thread Tools dropdown above first post.