Retrieve last full week in MS Query

[gj0519]

I have a query where I only want the previous weeks data.
I have it working in Access but I can not get this to work in Excel. Using Office 2k, querying DB2 database.
Any suggestions?

Thanks,

Gj

 ((Format([SHARE]![CLOSE_DATE],"wwyy"))=Format(DateAdd('ww',-1,Date()),"wwyy")))

 

[litvilkas]

Try Datediff

WHERE DATEDIFF('dd', CLOSE_DATE, CURRENT_DATE) = 7

 

[cheerio]

The code posted in the original question mixes single and double quotes and this is unlikely to work. Access tends to use double quotes. Most ODBC drivers and back end databases are going to want single quotes.

The datediff solution above gives a rolling 7 day selection which is not what the original code does. However, you could probably replace 'dd' with 'ww' and 7 with 1 to get back to fixed weeks.

 

[SkipVought]

Hi,

I prefer to calculate a date based on the run date. In my company, a report will either be run for the work week (Sat-Fri) or the nominal work week (Mon-Sun). So I use something like this...

Where MyDate Between INT((Date()-2)/7)*7+2 and INT((Date()-2)/7)*7+2+7

so no matter what day of the week I run the report, I get the SAME week range.

Skip,
[sub]
[red][/red]
[/sub]