Problem with Expression & weekending data. 1

[kentwoodjean]

I am not sure how to correct it but it appears that my formula for "Weekend" is the capturing the correct time frame. In order words, when I ran the query on Monday of this week, instead of capturing the totals for week ending 4/22, it picked up the totals for week ending 4/15. It always appears to be a week benind the most recent week ending. Can anyone see anything wrong in my SQL?

SELECT Avg([Count BA closedDeptCorp].DeptAge) AS AvgOfDeptAge, Avg([Count BA closedDeptCorp].CorpAge) AS AvgOfCorpAge, Sum(Nz([CountOfClosed Dt],0)) AS [CountClosed Dt], Sum(Nz([CountOfDate1],0)) AS CountDate1, [Closed Dt]-1-Weekday([Closed Dt]) AS Weekend
FROM [Countof Recd Date BA] RIGHT JOIN [Count BA closedDeptCorp] ON [Countof Recd Date BA].Date1 = [Count BA closedDeptCorp].[Closed Dt]
GROUP BY [Closed Dt]-1-Weekday([Closed Dt])
HAVING ((([Closed Dt]-1-Weekday([Closed Dt])) Like "*2005*"));

 

[dhookom]

To find the Sunday of a week, use
DateAdd("d",-Weekday([Closed Dt])+1,[Closed Dt])
Adding 6 days to this expression will get you the Saturday date.

You HAVING clause should be a WHERE clause and don't compare to the year with *s. Use the Year(your expression) function.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kentwoodjean]

Based on what you have indicated, here is what I have done. Instead of +1 I did -1 as I want my week to end on Friday. Someone, I am not following the 6 days? In order to deterimine how it works, I will need to run it on Monday but I wanted to see if I am understanding you.

SELECT Avg([Count BA closedDeptCorp].DeptAge) AS AvgOfDeptAge, Avg([Count BA closedDeptCorp].CorpAge) AS AvgOfCorpAge, Sum(Nz([CountOfClosed Dt],0)) AS [CountClosed Dt], Sum(Nz([CountOfDate1],0)) AS CountDate1
FROM [Countof Recd Date BA] RIGHT JOIN [Count BA closedDeptCorp] ON [Countof Recd Date BA].Date1 = [Count BA closedDeptCorp].[Closed Dt]
WHERE (((DateAdd("d",-Weekday([Closed Dt])-1,[Closed Dt])) Like "*2005*"));

 

[dhookom]

Your latest SQL doesn't have any grouping by date. Plus you again compared a date value with a string in your where clause.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kentwoodjean]

Thanks for the heads up. I'll fix that and give it a try on Monday to let you know if it is pulling the correct data.