set year in birthday Query

[action]

I have a birthday query (SQL) that I want to set an age period when running. We use this to send out birthday cards but have different ones according to age. I follow the query's logic ok but can't change it so that we can specify age groups ie ages 10 year olds to 15 year olds all having birthdays in the next few weeks. The SQL that is running is :

SELECT Members.Surname, Members.FirstName, Members.PhoneHome, Members.Sex, Members.Address1, Members.Address2, Members.Address3, Members.DateOfBirth, Members.email,

DateSerial(Year(Date()),Month([DateOfBirth]),Day([DateOfBirth])) AS MyDate FROM Members

WHERE ((Not (Members.DateOfBirth) Is Null) AND ((DateSerial(Year(Date()),Month([DateOfBirth]),Day([DateOfBirth]))) Between Date()+10 And Date()+24))
ORDER BY Members.DateOfBirth;


Can anybody help me adjust this so I can set year periods?

Thanks

 

[Fuga]

Can´t you use Datediff()?

Fuga.

 

[raskew]

Try adding this calculated field:

Expr1: Year(Now())-Year([DateOfBirth]) Between [enter low] And [enter high]

 

[action]

tried the expression but couldn't get it working

I tried the between now and then but couldn't get it working. This is on the right track but my skill is lacking on this, anybody give some further help?

Can't get my head around the diffdate.

Cheers

 

[raskew]

Took your query, stripped it of fields that don't contribute to the example, then used it to create a new query in Northwind.
Changed Members to Employees and renamed the fields as necessary and it works.

After testing in Northwind, try copying the SQL to a new query in your database, changing table/field names as necessary.
Provided it works as advertised, you can then add back the other fields.

SELECT Employees.LastName, Employees.FirstName, Employees.BirthDate, 
DateSerial(Year(Date()),Month([BirthDate]),Day([BirthDate])) AS MyDate, Year(Now())-Year([BirthDate]) AS Age
FROM Employees
WHERE ((Not (Employees.BirthDate) Is Null) AND
((DateSerial(Year(Date()),Month([BirthDate]),Day([BirthDate]))) Between Date()+10 And Date()+45)
AND ((Year(Now())-Year([BirthDate])) Between [enter low] And [enter high]))
ORDER BY Employees.BirthDate;