within 3

[slimjen]

All. I have a table with names and birthdates. I need to get a list of all people who are turning 65 within 3 months, 2 months and now. I started out with criteria "dob >90 days". I am drawing a blank. Please help.


Thanks

[June7]

Use DateDiff() function to calculate the date they will be 65. Might find this of interest https://www.accessforums.net/access/...age-34321.html

Apply criteria to that constructed field:

Between Date() AND Date()+90

[slimjen]

Ok. Thanks I got it. My code:

Age: Int((Now()+90-[DateofBirth])/365.25)

This gets me people who would be 65 within 3 months.

[slimjen]

Ok I thought I had it but it still doent work. Please help. thanks

[June7]

Did you try the suggestion I offered? I said calculate date, not age.

[slimjen]

Sorry; I'm not following. I thought I was calculating date.

[June7]

The expression you posted is calculating age. Since it returns whole number (years), could apply criteria to that constructed field: =65

However, since it is returning only the integer part, it will retrieve all records where the result is 65, even if birthday is 5 months from now.

Remove the Int() function and try criteria of: BETWEEN 65 AND 65.25

[slimjen]

So; Like this: Expr1: ((Now()+90-[DateofBirth])/365.25) with a criteria of Between 65 and 65.25. Still does get me anyone and I know there's at least l person. What am I doing wrong?

[June7]

Works for me. Post the SQL statement of attempted query.

[slimjen]

I got it this time. It returns the records I need and the Expr: returns 65.08..... What is this?Thanks so much.

SELECT tblARM1.ClientID, tblARM1.CSR, tblARM1.EEID, tblARM1.PolicyHolderSSN, tblARM1.DependentSSN, tblARM1.FirstName, tblARM1.LastName, tblARM1.Address1, tblARM1.Address2, tblARM1.City, tblARM1.Phone, tblARM1.State, tblARM1.Zip, tblARM1.ParticipantType, tblARM1.CoverageLevel, tblARM1.PlanName, tblARM1.ClientRate, tblARM1.TotalRate, tblARM1.EERate, tblARM1.DateofBirth, tblARM1.Age, ((Now()+90-[DateofBirth])/365.25) AS Expr1
FROM tblARM1
WHERE (((((Now()+90-[DateofBirth])/365.25)) Between 65 And 65.25));

[June7]

Your question: What is this? - refers to what? Expr1 name or the number 65.08?

[slimjen]

Sorry. This indicates a field I used to return the age or the time it takes to 65 within three months. I guess these are what the numbers stand for. Thanks