getting data from date range

[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

 

[John W. Vinson]

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:

 

[pat67]

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:

I will try that. if it is run on monday then it would be that day to
the previous monday

 

[pat67]

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:

Ok. That is not working. I am getting too much data.

 

[KARL DEWEY]

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.

 

[Marshall Barton]

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())

 

[John W. Vinson]

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.

 

[pat67]

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.

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

 

[pat67]

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?

 

[John W. Vinson]

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.

 

[pat67]

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.

thanks. I really appreciate it.