How to query based on anniversary of hire date? 1

[emzadi]

Hi. I have a personnel database for which I want a list of employees that have an anniversary date within 30 days to show up on my main form. The problem is crossing the year boundaries. If a person was hired July 4, 2001, for example, how do I get the query to return that record every July 4?

Thanks in advance,
Susan. Susan M. Wagner
LAPELS
emzadi1@yahoo.com
susanw@lapels.com

 

[TruthAndBeauty]

Morning,

Look atthe DateSerial function. This lets you control all three components of the date and can be very useful. It handles transitions between years.
Example: Give Date = 1/3/2002
DateSerial(Year(Date)-1, Month(Date) , Day(Date)-30) =12/03/2000

So your could say something like
If Hiredate = DateSerial(Year(Date)-1, Month(Date) , Day(Date)-30)

-----------------------------------------------------
For another alternative, investigate DATEPART function
DatePart Function Example
4-Jul-99 DatePart("m",[Hiredate]) --->7 (month of year)
4-Jul-99 DatePart("d",[Hiredate]) ---> 4 (4th day of month)
4-Jul-99 DatePart("yyyy",[Hiredate]) ---> 1999 (four-digit number of year)


Good luck

 

[emzadi]

Wow. That's exactly what I needed. I am using the DatePart function to split the year off. Thanks so much!! Susan M. Wagner
LAPELS
emzadi1@yahoo.com
susanw@lapels.com

 

[emzadi]

Well, actually, I'm still having trouble isolating all records with a date before now? Susan M. Wagner
LAPELS
emzadi1@yahoo.com
susanw@lapels.com

 

[emzadi]

I am so totally lost. I have been working with this DatePart function... however, when I split the dates apart, they are now treated as numbers, and 1 (January) is now earlier than 12 (December).

Does anyone have an easier way to compare anniversaries of dates??? Susan M. Wagner
LAPELS
emzadi1@yahoo.com
susanw@lapels.com