getting data from date range
in [Queries]
|
Prev: DateDiff
Next: Querying Dates
From: pat67 on 18 Mar 2010 11:53 hi here is what I need. I need to run a query that picks the data i specify from this week's Monday back to and including the previous week's Monday. i need it to run this way in case the query is not run on Monday. Example: if I were to run it today, i would get data from 3/15/2010 back to and including 3/8/2010. If i run it tomorrow, I get the same data. Any suggestions would be helpful. Thanks
From: John W. Vinson on 18 Mar 2010 12:53 On Thu, 18 Mar 2010 08:53:06 -0700 (PDT), pat67 <pbuscio(a)comcast.net> wrote: >hi here is what I need. I need to run a query that picks the data i >specify from this week's Monday back to and including the previous >week's Monday. i need it to run this way in case the query is not run >on Monday. Example: > >if I were to run it today, i would get data from 3/15/2010 back to and >including 3/8/2010. If i run it tomorrow, I get the same data. Any >suggestions would be helpful. Thanks That's a bit ambiguous. What range of dates do you want if the query *IS* run on Monday? Two weeks ago through one week ago, or from a week ago through today? If the date/time field contains a time portion do you want to see the data *on* this week's Monday, or only up to midnight at the beginning of Monday? As posted try: >= DateAdd("d", -6-Weekday(Date(), 2) , Date()) AND < DateAdd("d",1-weekday(Date(),2),Date()) -- John W. Vinson [MVP]
From: pat67 on 18 Mar 2010 13:00 On Mar 18, 12:53 pm, John W. Vinson <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote: > On Thu, 18 Mar 2010 08:53:06 -0700 (PDT), pat67 <pbus...(a)comcast.net> wrote: > >hi here is what I need. I need to run a query that picks the data i > >specify from this week's Monday back to and including the previous > >week's Monday. i need it to run this way in case the query is not run > >on Monday. Example: > > >if I were to run it today, i would get data from 3/15/2010 back to and > >including 3/8/2010. If i run it tomorrow, I get the same data. Any > >suggestions would be helpful. Thanks > > That's a bit ambiguous. What range of dates do you want if the query *IS* run > on Monday? Two weeks ago through one week ago, or from a week ago through > today? If the date/time field contains a time portion do you want to see the > data *on* this week's Monday, or only up to midnight at the beginning of > Monday? > > As posted try: > > >= DateAdd("d", -6-Weekday(Date(), 2) , Date()) AND < DateAdd("d",1-weekday(Date(),2),Date()) > > -- > > John W. Vinson [MVP] I will try that. if it is run on monday then it would be that day to the previous monday
From: pat67 on 18 Mar 2010 13:03 On Mar 18, 12:53 pm, John W. Vinson <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote: > On Thu, 18 Mar 2010 08:53:06 -0700 (PDT), pat67 <pbus...(a)comcast.net> wrote: > >hi here is what I need. I need to run a query that picks the data i > >specify from this week's Monday back to and including the previous > >week's Monday. i need it to run this way in case the query is not run > >on Monday. Example: > > >if I were to run it today, i would get data from 3/15/2010 back to and > >including 3/8/2010. If i run it tomorrow, I get the same data. Any > >suggestions would be helpful. Thanks > > That's a bit ambiguous. What range of dates do you want if the query *IS* run > on Monday? Two weeks ago through one week ago, or from a week ago through > today? If the date/time field contains a time portion do you want to see the > data *on* this week's Monday, or only up to midnight at the beginning of > Monday? > > As posted try: > > >= DateAdd("d", -6-Weekday(Date(), 2) , Date()) AND < DateAdd("d",1-weekday(Date(),2),Date()) > > -- > > John W. Vinson [MVP] Ok. That is not working. I am getting too much data.
From: KARL DEWEY on 18 Mar 2010 13:06
>>from this week's Monday back to and including the previous week's Monday. Your wording says you want 8 days of data. Between DateAdd("d",-Weekday(Date())+2,Date()) AND DateAdd("d",-Weekday(Date())-5,Date()) Bear in mind that this works with dates that do not have any time associated with them. -- Build a little, test a little. "pat67" wrote: > hi here is what I need. I need to run a query that picks the data i > specify from this week's Monday back to and including the previous > week's Monday. i need it to run this way in case the query is not run > on Monday. Example: > > if I were to run it today, i would get data from 3/15/2010 back to and > including 3/8/2010. If i run it tomorrow, I get the same data. Any > suggestions would be helpful. Thanks > . > |