Calculating Age for given date 1

[hianjali]

Hello All,

Thanks for the wonderful responses and i am beginning to learn Access now

Here, I need to pull all the members who have more than 110 years of age as of today's date using Access 97

Here is my Query:

SELECT elgdep.MEM_SOC_SEC_NUM, elgdep.MEM_DEP_CODE, elgdep.MEM_BIRTH_DATE, elgdep.MEM_EFFEC_DATE
FROM elgdep
WHERE (((elgdep.MEM_BIRTH_DATE)>(Format([Today's Date]/365.25,"yyyymmdd")>110)));

I am getting an error "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)" Please help

 

[Melagan]

This approach might work:

SELECT elgdep.MEM_BIRTH_DATE, (Now()-[MEM_BIRTH_DATE])/365.25 AS MEM_AGE
FROM elgdep
WHERE ((((Now()-[MEM_BIRTH_DATE])/365.25)>110));

Here we are calculating age and giving that value it's own column; we can then specify criteria on that column we just created.

This also avoids having to have a "current date" field in your database or as a parameter in your query =)


~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

Provided that elgdep.MEM_BIRTH_DATE is a pseudo-date field formatted as yyyymmdd:
SELECT ...
FROM ...
WHERE elgdep.MEM_BIRTH_DATE < Format(DateSerial(Year(Date())-110,Month(Date()),Day(Date())),'yyyymmdd')

If elgdep.MEM_BIRTH_DATE is a real Date field:
...
WHERE elgdep.MEM_BIRTH_DATE < DateSerial(Year(Date())-110,Month(Date()),Day(Date()))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hianjali]

Thanks a lot Melagan and PHV...PHV..it worked perfect with

Provided that elgdep.MEM_BIRTH_DATE is a pseudo-date field formatted as yyyymmdd:
SELECT ...
FROM ...
WHERE elgdep.MEM_BIRTH_DATE < Format(DateSerial(Year(Date())-110,Month(Date()),Day(Date())),'yyyymmdd')

Thanks a ton