Querying for people who will turn a certain age during a specific year 1

[jennifer21277]

Hi All,

I am trying to set up a process that will query for any person who turns age 65 during a specific quarter. This process will be used in the future, so I want to automate the task. Currently, in the criteria I use the following DatePart expresssion: Int(DateDiff('d',[BIRTH_DTE],Now())/365.25)=65. This is almost accurate. If I were to run this query today, the results would include anyone born between 3/20/1938 and 3/19/1939, but in actuality, I would only want those who are born between 1/1/1939 and 3/31/1939. In order for this work properly, the query would have to be run on the last day of the quarter. This may not always be possible, therefore I would like the person who will run this report to be able to enter the date range for the quarter as a parameter where the results will include all those who have a birth date in that quarter and will turn age 65 for the current year. Is this at all possible? Thanks in advance for any assistance you offer.

 

[Terpsfan]

This function will return whether the person is 65 or not. I just created 2 text boxes on the form and a command button. On the command button click event I ran this function
txtCurrentAge = Format(Now()-txtBirthdate,"yy")

 

[Terpsfan]

I thought of 2 queries that might help you. The first determines the date that the person turns 65, the second groups and orders them by quarter.
qryBirthdate65
SELECT LastName, DateAdd("yyyy",65,[Birth_Date]) As Date65
FROM tblEmployees;

SELECT LastName, DatePart("q",[Date65]) As Qtr65
FROM qryBirthdate65
GROUP BY LastName, DatePart("q",[Date65])
ORDER BY DatePart("q",[Date65])
You could always designate a specific quarter in the 2nd query.
take out the group and order by statements, replace with
WHERE DatePart("q",[Date65]) = 2;

 

[BobJacksonNCI]

Jenny, (A bit presumptious, aren't I?)

Try this:

[BIRTH_DTE] BETWEEN DateSerial(Year(Date())-65, Int((Month(Date()) - 1) / 3) * 3 + 1, 1) AND DateSerial(Year(Date())-65, Int((Month(Date()) - 1) / 3) * 3 + 4, 0)

HTH,
Bob