Extracting Month and day from Date field

W

Williams

I need to create a query based upon the anniversay of employee hire dates.

Records should be limited to only those employees with anniversaries within
the next 60 days, so we can do annual reviews. How to I create another field
in a query that extracts just the month & date from the DateHired, and search
that field to see if the month and date are within 60 days from the current
date.
 
M

Marshall Barton

Williams said:
I need to create a query based upon the anniversay of employee hire dates.

Records should be limited to only those employees with anniversaries within
the next 60 days, so we can do annual reviews. How to I create another field
in a query that extracts just the month & date from the DateHired, and search
that field to see if the month and date are within 60 days from the current
date.


Several ways to do that. A relatively simple way is to use
a calculated field:

MonDay: Format(annivdate, "mmdd")

and the criteria would then be:

Between Format(Date(), "mmdd") And Format(DateAdd("d", 60,
Date(), "mmdd"))
 
J

John Spencer

Try the following

Field: DateSerial(Year(Date()),Month(DateHired),Day(DateHired))
Criteria: Between Date() and DateAdd("d",60, Date()) OR Between
DateAdd("yyyy",-1,Date()) AND
DateSerial(Year(Date())-1,Month(Date()),Day(Date())+60)

You need the second part of the criteria to catch those whose DateHired is
in the last 60 days of the year
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top