How to scan for an aniversary date without using years?

[BukHix]

I have a table with these 2 fields: Name and AnniversaryDate. What is the best way to write a query that will show me all the anniversaries that will fall within the next two weeks?

For instance if I have a record with these values:

Name, Anniversary
BukHix, 1/1/2003

I want the query to look at today’s date, which in this case would be 12/29/2005 go two weeks forward, which would make it 1/12/2006 and show any anniversary date that would fall between the dates.

The catch (and the part that is throwing me off) is how do I do that without explicitly naming the year? I mean I need to catch every date in that range from 1965 to Current year - 1.

Any ideas?

 

[llkhoutx]

I would unpack the day and the month, then check for that.

You can't just use days since 12/31 because of leap year.

Search this sight for birthday. You will get other insights.

 

[BukHix]

Thank you. I tried doing a search using different criteria and didn't find anything useful. When I did it by birthday as you suggested I found the answer in the first couple of threads.

If anyone else is looking to this you can find an answer here.

 

[BukHix]

I spoke too soon. The one flaw with that solution is that the

BirCheck: DateSerial(Year(Date()),Month([Birthday]),Day([Birthday]))

Will only consider dates in the current year so if we have anyone with anniversary dates in the first couple of weeks of January they will get skipped.

Once the current year is 2006 it will be ok but I have to find a better solution. I'm going back to search some more.

 

[BukHix]

Now I have it. I stole this function from this thread:

Function NextBirthday(aDOB As Date) As Date
    NextBirthday = DateSerial(Year(Date), Month(aDOB), Day(aDOB))
    If NextBirthday < Date Then NextBirthday = DateSerial(Year(Date) + 1, Month(aDOB), Day(aDOB))
End Function

And then I wrote my query like this:

SELECT EmpDates.ID, EmpDates.Name, EmpDates.Birthday,  NextBirthday([Birthday]) AS BirthdayCheck
FROM EmpDates
WHERE (((NextBirthday([Birthday])) Between Date() And Date()+14))
ORDER BY NextBirthday([Birthday]);