getting data from date range
in [Queries]
|
Prev: DateDiff
Next: Querying Dates
From: Marshall Barton on 18 Mar 2010 13:21 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. The criteria for the date field could be something like: Between DateAdd("d", -6-WeekDay(Date(),2), Date()) And DateAdd("d", 1-WeekDay(Date(),2), Date()) -- Marsh MVP [MS Access]
From: John W. Vinson on 18 Mar 2010 13:23 On Thu, 18 Mar 2010 10:03:16 -0700 (PDT), pat67 <pbuscio(a)comcast.net> wrote: >> >= 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. Sorry, my crystal ball is a bit foggy... Please post your actual query, some sample data, and some of the dates that you're seeing that you don't want to see. -- John W. Vinson [MVP]
From: pat67 on 18 Mar 2010 13:37 On Mar 18, 1:23 pm, John W. Vinson <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote: > On Thu, 18 Mar 2010 10:03:16 -0700 (PDT), pat67 <pbus...(a)comcast.net> wrote: > >> >= 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. > > Sorry, my crystal ball is a bit foggy... > > Please post your actual query, some sample data, and some of the dates that > you're seeing that you don't want to see. > -- > > John W. Vinson [MVP] you guys are all right and i am an idiot. all three are working. what i am doing is a count of lines between those dates. for some reason when i filtered for the dates in my table, all of the lines were not picked up so when i checked with what you guys said, the totals were off. They are correct no. Thanks
From: pat67 on 18 Mar 2010 13:55 On Mar 18, 1:37 pm, pat67 <pbus...(a)comcast.net> wrote: > On Mar 18, 1:23 pm, John W. Vinson > > > > > > <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote: > > On Thu, 18 Mar 2010 10:03:16 -0700 (PDT), pat67 <pbus...(a)comcast.net> wrote: > > >> >= 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. > > > Sorry, my crystal ball is a bit foggy... > > > Please post your actual query, some sample data, and some of the dates that > > you're seeing that you don't want to see. > > -- > > > John W. Vinson [MVP] > > you guys are all right and i am an idiot. all three are working. what > i am doing is a count of lines between those dates. for some reason > when i filtered for the dates in my table, all of the lines were not > picked up so when i checked with what you guys said, the totals were > off. They are correct no. Thanks- Hide quoted text - > > - Show quoted text - I actually do have one more question for any of you guys. The query is working like i said, but could you explain to me hwo each part works and how it dissemintaes the data?
From: John W. Vinson on 18 Mar 2010 15:14
On Thu, 18 Mar 2010 10:55:02 -0700 (PDT), pat67 <pbuscio(a)comcast.net> wrote: >I actually do have one more question for any of you guys. The query is >working like i said, but could you explain to me hwo each part works >and how it dissemintaes the data? Glad you got it working. To break this down: >= DateAdd("d", -6-Weekday(Date(), 2) , Date()) AND < DateAdd("d",1-weekday(Date(),2),Date()) Work from the inside out. The Date() function returns today's date from the computer clock. The Weekday() function returns the day of the week; the (optional) 2 after the date means to start the week on Monday rather than the default Sunday; the function returns numbers 1 through 7, with 1 meaning Monday, 2 Tuesday and so on. So since today is 3/18, Weekday(Date(), 2) is 4 (Mon, Tue, Wed, Thu). Subtracting 4 days from today using the DateAdd function, and then 6 more days from that ( the -6-Weekday bit) gives the date of Monday last week. The second dateadd does the same thing for the date of Monday this week. -- John W. Vinson [MVP] |