DateAdd Question

[kentwoodjean]

Need just a bit of help please. Have looked at multiple postings. Have a query with the Field "ToDate" that I have changed to: Weekending: [ToDate]-2 so that I can caputure data each week ending on a Friday. works great but now need to set the query to capture the most recent last 4 weeks each time it is run. Here is what I thought I should do: Expr1: DateAdd('ww',-4,[WeekEnding]) but the result is that it gives me a field that subtracts 4 weeks from the current weekending date each week of the entire year. I don't want to change my weekending data, just capture the last 4 weeks on an ongoing basis. How does this function need to be adjusted?

 

[DotNetGnat]

i think you need to do this in separate variables...

Weekending: [ToDate]-2
Weekending1: DateAdd('ww',-1,[ToDate])-2
Weekending2: DateAdd('ww',-2,[ToDate])-2
Weekending2: DateAdd('ww',-3,[ToDate])-2

did you get what i am trying to say...

-DNG

 

[kentwoodjean]

Sorry I am just getting back to you as I have been tied up since fist sending the post.

This maybe a stupid questions but are you indicating that I need to do 4 queries and combine them all for the result I require?

 

[kentwoodjean]

Still looking for my result. Whether I put 1 or 3 of the additional varaiables on my query as suggested, I get the correct dates minus the data. Even tried running another query with a criteria of Between Weekending and Weekending 2 and that did not work. Again, my result continues to show all weeks data.

I found another post out on the MS discussion forum very similar to my request. this person also wanted the current and previous 3 weeks data for a total of 4 weeks of the most current data.

Here is the suggestion that was posted on the MicroSoft Here are the. Here is the result that was proved:

Between DateAdd(“ww”,-3,Date()-Day(Date()))-Day(DateAdd(“ww”,-3,Date()-Day(Date())))+1 and Date()-Day(Date())).

This is exactly what I am trying to do. In my "ToDate" Field, I have entered the above as a criteria. When I run it, I get the error "too many closing parenthesis". If I remove the last one, I get the erorr "invald procedure call". Couple of questions. (1) Is this the correct criteria to use to obtain the current weekending data as well as the previous 3 weeks data? (2) if this is correct, do I show the criteria under my "Weekending" field or the "ToDate" field? I sure could use a bit a help here as I am a bit confused.

 

[DotNetGnat]

Between 
[red]([/red][blue]DateAdd(“ww”,-3,(Date()-Day(Date())))[/blue]-
[green]Day(DateAdd(“ww”,-3,(Date()-Day(Date()))))[/green][red])[/red] + 1 
and 
( [red]Date()-Day(Date())[/red]).

-DNG

 

[kentwoodjean]

Thanks for the quick response. Keep in mind that I am somewhat of a newbie when I ask my questions. As you may recall, I changed the Field "ToDate" to: Weekending: [ToDate]-2 so that I could capture each week ending on a Friday.

Therefore, does my code go in the criteria line for the new field "Weekending" or do I add my previous field name "ToDate" and include the criteria line there? I have tried putting the code under either of these field names and in each case I get the error "Invalid Procedure".
Below is my SQL where it looks like additional parenthesis were automatically added to the end.

SELECT ([AvgWrap]+[AvgAtt]) AS ACHT, [ToDate]-2 AS Weekending, [SumOfAns]/[SumOfTotHandled] AS AvTSF, Sum([CS Natl wk].TotOffer) AS SumOfTotOffer, Sum([CS Natl wk].TotHandled) AS SumOfTotHandled, Sum([CS Natl wk].TotAband) AS SumOfTotAband, Avg([CS Natl wk].ASA) AS AvgOfASA, [SumofTotAband]/[SumOfTotOffer] AS PercentOfCanc, Sum([CS Natl wk].As) AS SumOfAs, [SumOfAs]/[SumOfTotHandled] AS AvgASA, Sum([CS Natl wk].Ans) AS SumOfAns, AvgEmpPID.Avg AS Expr1, Sum([CS Natl wk].Aw) AS SumOfAw, Sum([CS Natl wk].At) AS SumOfAt, [SumOfAw]/[SumOfTotOffer] AS AvgWrap, [SumOfAt]/[SumOfTotHandled] AS AvgATT, [CS Natl wk].ToDate
FROM [CS Natl wk] INNER JOIN AvgEmpPID ON [CS Natl wk].ToDate = AvgEmpPID.Weekending
GROUP BY [ToDate]-2, AvgEmpPID.Avg, [CS Natl wk].ToDate
HAVING ((([CS Natl wk].ToDate) Between (DateAdd("“ww”",-3,(Date()-Day(Date())))-Day(DateAdd("“ww”",-3,(Date()-Day(Date())))))+1 And (Date()-Day(Date()))));

 

[kentwoodjean]

I have been out of town working on contingency plans for impending strike. Any suggestions as to under which field I put the criteria shown by DotNetGant and why I am getting the Calling Procedure error?