Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query between dates regardless of year

Status
Not open for further replies.

Rufusjeep

Technical User
Nov 6, 2001
10
US
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
 
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
 
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
 
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. [spin]

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top