Simple Anniversary Date query.

[DraGo150]

This is probably very simple, but it has eluded me for two days now. What I am looking for is a Between criteria in a query that will allow the user to input the beginning month, day and ending month, day to search for anyone with a anniversary date in the chosen date range. I am trying not to have to input the year because we have employees that have been here for over thirty years. Can anyone give me any suggestions? Thank you.

 

[Golom]

I don't think that a BETWEEN clause will do it. Try something like

WHERE 

((Month(HireDate) = StartMonth AND Day(HireDate) >= StartDay)
  OR Month(HireDate) > StartMonth)

AND

((Month(HireDate) = EndMonth AND Day(HireDate) < EndDay)
  OR Month(HireDate) < EndMonth)

 

[Golom]

Note however that this assumes that the Start and End months are in the same year. If you wanted, for example to have

StartMonth = 12, StartDay = 1
EndMonth = 2, EndDay = 28

Then no records would qualify because the "End" is before the "Start" and that's because we are ignoring the year.

 

[danvlas]

What if you want a range spanning 2 years? From 12/01/2010 to 04/01/2011?

Where Year(StartDate) * 10000 + Month(DOB)*100 + Day(DOB) >=Year(StartDate) * 10000 + Month(StartDate) * 100 + Day(StartDate)
and
Year(EndDate) * 10000 + Month(DOB)*100 + Day(DOB) <=Year(EndDate) * 10000 + Month(EndDate) * 100 + Day(EndDateDate)

Hope I pulled it right

HTH



Daniel Vlas
Systems Consultant

 

[DraGo150]

Golom, Thank for replying.

Your assistance was perfect. That was what I was looking for.

And Daniel i will keep your code for future endeavors.

Thank all,