Convert today's date -7 days to week YYYYWW (last week)

[AFSSkier]

Is there a more efficient syntax to convert today's date -7 days to week
YYYYWW (last week), then the SQL below? Result = 200847

WHERE (((a11.week_id)=Format(Date()-7,"yyyy") &
Format(Format(Date()-7,"ww"),"00"))) This took 16 mins.

Replaced hardcode: WHERE (((a11.week_id) In (200847))) This took only 5 secs.

SELECT DISTINCT a12.department_id, a13.department_desc,
(0.001+Nz(Sum(a11.sales_d_r52w),0)) AS Sales_D
FROM (division_r52w AS a11 INNER JOIN p_item AS a12 ON a11.item_id =
a12.item_id) INNER JOIN p_department AS a13 ON a12.department_id =
a13.department_id
WHERE (((a11.week_id)=Format(Date()-7,"yyyy") &
Format(Format(Date()-7,"ww"),"00")))
GROUP BY a12.department_id, a13.department_desc, a11.week_id
ORDER BY a12.department_id;

 

[Beetle]

You might try;

Format(DateAdd("ww", -1, Date()), "yyyyww")

 

[AFSSkier]

Beetle,

Your suggestion does work a little faster. But the query still takes 15
minutes to run. Where the hardcoded IN (200847) only takes 5 sec. to run.

Thanks, Kevin

 

[John Spencer]

Try forcing the result to be a number value instead of a string value.

Year(DateAdd("d",-7,Date()))*100 + DatePart("ww",DateAdd("d",-7,Date()))

I think (no proof) that
if Week_ID is a number field then Access was converting the text string you
were generating to a number and then doing the compare or it was converting
the field to a string and then doing the compare. In either of those cases it
was probably unable to use any index on the Week_ID field.

Anyway, I would be interested to hear about your results.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[AFSSkier]

John,

That worked perfectly. The field is an integer. The orginal query ran in 5
sec.
CurrWeek LastWeek
200848 200847

I also tested it against the 1st week of next year for previous week (see
SQL).

The following test SQL, anyone can run to see the result.
SELECT Year(DateAdd("d",0,Date()))*100 +
DatePart("ww",DateAdd("d",0,Date())) AS [CurrWeek (YYYYWW)],
Year(DateAdd("d",-7,Date()))*100 + DatePart("ww",DateAdd("d",-7,Date())) AS
[LastWeek (YYYYWW)],
Year(DateAdd("d",-7,Date())) AS [Year],
DatePart("ww",DateAdd("d",0,Date())) AS CurrWk,
DatePart("ww",DateAdd("d",-7,Date())) AS LastWk
Year(DateAdd("d",-7,#12-28-2008#))&DatePart("ww",DateAdd("d",-7,#12-28-2008#)) AS [LastWk 12/28/2008 (1stSunday)]
Year(DateAdd("d",-7,#01-03-2009#))&DatePart("ww",DateAdd("d",-7,#01-03-2009#)) AS [LastWk 01/03/2009 (1stSaturday)];