Problem with birthdays

[DJBummy]

I have a query that extracts the month and day of a bowlers birthday for a selected week and year (Done from a form).
The query below works fine until the end of the year when the ScheduleDate crossover to a new year. At that point the query wants to include all of the bowlers birthdays even though I'm only trying to get the month/Day Between 12/31/07 and 01/06/08.

What am i doing wrong?

Hope I'm clear enough!


SELECT tblBowlers.FirstName, tblBowlers.LastName, tblBowlers.BirthDay, Format(DateDiff("yyyy",[BirthDay],[ScheduleDate]),"#") AS BowlerAge, tblSchedules.SeasonID, tblSeasons.Quit
FROM tblSchedules, tblTeams INNER JOIN (tblBowlers INNER JOIN tblSeasons ON tblBowlers.BowlerID = tblSeasons.BowlerID) ON tblTeams.TeamID = tblSeasons.TeamID
GROUP BY tblBowlers.FirstName, tblBowlers.LastName, tblBowlers.BirthDay, tblSchedules.SeasonID, tblSeasons.Quit, tblSchedules.ScheduleDate, tblSchedules.WeekNo
HAVING (((tblBowlers.FirstName)<>"DUMMY") AND ((tblBowlers.LastName)<>"DUMMY") AND ((tblSchedules.SeasonID)=[Forms]![frmYearSelect].[SeasonYearID]) AND ((tblSeasons.Quit)=False) AND ((tblSchedules.WeekNo)=[Forms]![frmWeekSelectExcel].[txtNextWeek]) AND ((Format([BirthDay],"mm/dd")) Between Format([ScheduleDate]-2,"mm/dd") And Format([ScheduleDate]+4,"mm/dd")));

 

[Guus2005]

Please post a sample database.
It is easier for us to provide you with a sound solution.

My birthday is in December. according to your calculation i am a year older in January.