Re: How to select between a range of dates?
From: John Spencer (MVP) (spencer4_at_comcast.net)
Date: 11/02/04
- Next message: Anna S: "Why .RecordCount() not working"
- Previous message: John Spencer (MVP): "Re: Query to count check boxes"
- In reply to: 04Ram: "Re: How to select between a range of dates?"
- Next in thread: 04Ram: "Re: How to select between a range of dates?"
- Reply: 04Ram: "Re: How to select between a range of dates?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 01 Nov 2004 19:20:13 -0500
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.
04Ram wrote:
>
> 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.
>
> "John Spencer (MVP)" wrote:
>
> > 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.
> >
> >
> > 04Ram wrote:
> > >
> > > 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?
> >
- Next message: Anna S: "Why .RecordCount() not working"
- Previous message: John Spencer (MVP): "Re: Query to count check boxes"
- In reply to: 04Ram: "Re: How to select between a range of dates?"
- Next in thread: 04Ram: "Re: How to select between a range of dates?"
- Reply: 04Ram: "Re: How to select between a range of dates?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
