Query dates before last week not working

[skwilliams]

I need to query the dates prior to last week going into last year. But if I try to use the <=DatePart ("ww", dtDate) criteria it won't pull dates from later in the previous year. I would just use <Date()-7 but I may not query this data on the same day each week.

Any other workarounds??

Thanks.

 

[pbaldy]

You should find a function here to get the appropriate day of the previous week:

http://www.pacificdb.com.au/Support/code_datetime.htm

 

[skwilliams]

These functions don't seem to apply.

As I said, the week function won't give me the dates from last year except those that fall within that parameter and the day function won't work because it would reply on the query being executed on a specific day of the week.

So for example, I'd like to pull all the dates from 5/18/2009 through last week.

 

[pbaldy]

So a function that found the date for the Monday of last week (or whatever) wouldn't help you with a criteria like:

Between #5/18/09# And ThatDate

 

[skwilliams]

That might help but I don't see a function for it.

 

[pbaldy]

Follow the "Week functions" link and then functions such as this:

First day of previous week (Monday = day 1)
Last day of previous week (Monday = day 1)

 

[raskew]

Hi -

NOTE: In each of the following examples I've used the format() function to display the resulting date in 'long date' format. This was done only to make the examples more readable. In actual practice I don't see a purpose for this.

? format(date(), "long date")
Thursday, January 14, 2010

...the following will return the Saturday of the previous week, regardless of the day it is run:
? format(dateadd("d", - weekday(date()), date()), "long date")
Saturday, January 09, 2010

...if you want to return records with dates less than the Monday of the previous week, modify the above:
? format(dateadd("d", - weekday(date()) -5, date()), "long date")
Monday, January 04, 2010

HTH - Bob

 

[skwilliams]

That worked.

Thanks.