getting data from date range

  • Thread starter Thread starter pat67
  • Start date Start date
P

pat67

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
 
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
Click to expand...

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:
 
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:
Click to expand...

I will try that. if it is run on monday then it would be that day to
the previous monday
 
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:
Click to expand...

Ok. That is not working. I am getting too much data.
 
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.
 
Ok. That is not working. I am getting too much data.
Click to expand...

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.
 
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.
Click to expand...

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
 
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 -
Click to expand...

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?
 
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?
Click to expand...

Glad you got it working. To break this down:
= DateAdd("d", -6-Weekday(Date(), 2) , Date()) AND < DateAdd("d",1-weekday(Date(),2),Date())
Click to expand...

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.
 
Glad you got it working. To break this down:


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 thanthe
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 moredays
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.
Click to expand...

thanks. I really appreciate it.
 
Back
Top