Query between dates regardless of year

[Rufusjeep]

Have a table with all of the employees in it with their hire dates. What I need to be able to do is query out any who was hired between 9/15 and 9/29, do not care what year they were hired in.
Tried using "Between [First Date] and [Second Date]"

This does not give me the resultes that I am looking for. What do I need to change to get it to work.

Thanks
Mike

 

[RickBerem]

Hi,

Maybe you should try the MONTH and DAY fonction
to build something like :

Month([HireDate]) Day([HireDate])
=9 between 15 And 29

Hope i'm clear enough.

Salvatore Grassagliata
GrassagliataS@hotmail.com

 

[Rufusjeep]

You suggestion gives the results that I am looking for, however is there a way that I can just enter in the two dates, the begining and end dates ( i.e. 9/25 and 10/8).

That way there would be 4 boxes that have to have number entered into them.

This is what I have got going on so far:
Field: Month([Hire_Date])
Criteria: Between [Begin Date] and [End Date]

Field: Day([Hire_date])
Criteria: Between [Begin] and [End]

Thanks,
Mike

 

[SgtJarrow]

My recommendation would be the following....

Assumptions: Your date format is 09/04.....If not, the below code would still work but would require some major tweaking.....

Instead of using a parameter query, create a text box on a form (could be a pop-up form with just these two boxes) where you enter the start and end dates. Then, you can use the Left and Right Functions to pass the criteria to the query....

Field: Month([Hire_Date])
Criteria: Between Left(Forms![FormName]![Start Date Box],2) and Left(Forms![FormName]![End Date Box],2)

Field: Day([Hire_Date])
Criteria: Between Right(Forms![FormName]![Start Date Box],2) and Right(Forms![FormName]![End Date Box],2)

This will take the two boxes and break it into the parts needs for each. If you need more help with something like this, please just say so, and I'll be glad to help. "Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." Albert Einstein.

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[Rufusjeep]

Getting closer to the results that I am looking for.

My date format is 9/04/2000.

The code that I am using is sort of working, however when I input 4/18 as start and 5/1 as end date. The problem is that it returns hire dates of 4/13, 4/5,... not 4/20, 4.25,.. like I was expecting it to do.

This is the code:
Field: Month([Hire_Date])
Criteria: Between [Begin Date] and [End Date]

Field: Day([Hire_date])
Criteria: Between [Begin] and [End]

what do I need to change so that it returns the results that I am expecting.

Thanks, Mike