How to select between a range of dates?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to set up a query that finds hire date between a range of dates.
I know that I can use the between function to do this. It is irregardless of
year, just on the month and day. e.g. January reviews are from 11/16 to
02/15. Is there a way to do this without using the year?
 
Would a single parameter like this work for you:

Between [StartDate] And (DateAdd("m", 1, [StartDate]) - 1)
 
Interesting problem. Because of the year wrap, the January Reviews would be
based on

WHERE Format(HireDate,"mmdd") BETWEEN "1116" and "1231" OR
Format(HireDate,"mmdd") BETWEEN "0101" and "0215"

The other 3 quarters would be simpler

WHERE Format(HireDate,"mmdd") BETWEEN "0216" and "0515"

WHERE Format(HireDate,"mmdd") BETWEEN "0516" and "0815"

WHERE Format(HireDate,"mmdd") BETWEEN "0816" and "1115"

I will have to ponder awhile on how to generalize this. Or hope someone else
has already worked out a method.
 
MVP this really helped me out. I have one more question though. Until an
associate reaches 2 years their review is dependant on their hire date. For
example an associate was hired on 02/12/04. They have a 90 day review, a 9
month review, and a 18 month review. When they hit 2 years then their review
date is what I described before. How can I incorporate this into the query so
that I can only get those associates that have more than 2 years and another
one that retrieves those under 2 years. The problem is that those under 2
years would be year dependant. I am guessing that this would be 2 different
querries. Thanks.
 
Need more details on how you decide someone is due a 90 day review or a 9 month
review or an 18 month review.

I'm going to guess that you want to get them if they fall in the quarter.

WHERE DateAdd("D",90,HIREDATE) Between #02/16/2004# AND #05/15/2004# OR
DateAdd("M",9,HIREDATE) Between #02/16/2004# AND #05/15/2004# OR
DateAdd("M",18,HIREDATE) Between #02/16/2004# AND #05/15/2004# OR
Format(HireDate,"mmdd") BETWEEN "0216" and "0515"

That won't flag which type of review is due (Standard Annual, 90 day, etc) but
it will select the people for review.
 
A 90 day, 9 month, or 18 month review is based strictly on their hire date.
ie if someone was hired on 01/01/04 then their 90 day review would be due 90
days from their hire date, and then 9 months from their hire date, and 18
months from their hire date. It does not depend on the quarter at all. For
instance you would have reviews due for new hires that are under the 2 year
rule for every month. Only when they reach their 2 year mark would they move
to the quarterly review cycle. This can be on two separate queries. I hope
that this helps.
 
One more thing is that the associates who have more than two years should not
include those associates under 2 years and the other way as well.
 
I think that I will have to bow out of this thread. You are right that you will
need to use date ranges to identify the people doue a review. The simplest way
is to use the DateAdd function to increase the date and then check to see if
that falls in the review range. For example to get all people due a 90 day
review in the current month.

WHERE DateAdd("d",90,[HireDate])
Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0)

You can obviously use hard-coded dates for this.
 
Back
Top