week (WW) conversion to date in Access Graph

[Cangioli]

I'm creating a graph in a report to show week-by-week progressions.
Unfortunately, my X-axis want to display the number of the week (38, 39, 40,
etc..). How can I get it to display the weekend ending date instead and
still keep the weekly sum of the daily values? Here is my SQL code
SELECT (Format([Actual Date],"WW 'YY")) AS Expr1,
Sum(qryGraphInHome02Off.Incoming) AS SumOfIncoming
FROM qryGraphInHome02Off
GROUP BY (Format([Actual Date],"WW 'YY")), (Year([Actual
Date])*CLng(54)+DatePart("ww",[Actual Date],0)-1);
Any help is appreciated. Thanks.

 

[Duane Hookom]

SELECT DateAdd("d",-Weekday([Actual Date])+7,[Actual Date]) AS WeekEnding,
Sum(qryGraphInHome02Off.Incoming) AS SumOfIncoming
FROM qryGraphInHome02Off
GROUP BY DateAdd("d",-Weekday([Actual Date])+7,[Actual Date]);

 

[Cangioli]

You are awesome!!!
Its perfect. Exactly what I was looking for.

SELECT DateAdd("d",-Weekday([Actual Date])+7,[Actual Date]) AS WeekEnding,
Sum(qryGraphInHome02Off.Incoming) AS SumOfIncoming
FROM qryGraphInHome02Off
GROUP BY DateAdd("d",-Weekday([Actual Date])+7,[Actual Date]);

--
Duane Hookom
Microsoft Access MVP

I'm creating a graph in a report to show week-by-week progressions.
Unfortunately, my X-axis want to display the number of the week (38, 39, 40,
etc..). How can I get it to display the weekend ending date instead and
still keep the weekly sum of the daily values? Here is my SQL code
SELECT (Format([Actual Date],"WW 'YY")) AS Expr1,
Sum(qryGraphInHome02Off.Incoming) AS SumOfIncoming
FROM qryGraphInHome02Off
GROUP BY (Format([Actual Date],"WW 'YY")), (Year([Actual
Date])*CLng(54)+DatePart("ww",[Actual Date],0)-1);
Any help is appreciated. Thanks.