Finding last week

[jgelpi16]

I have a bit of a dilemma. I have a query that can run any time during the week. In the table the query runs on there is a date field. What I need to do is select any records that had last week's date on it. I am struggling with this one a bit and any help will be much appreciated.

[jgelpi16]

Here is the SQL that I have attempted in MS Access 2007. I have limited knowledge on SQL code so I need some help with this I am sure. I get an "Invalid SQL Statement Error" on the "DECLARE" portion. Thanks a lot for your help!

DECLARE @TodayDayOfWeek INT
DECLARE @EndOfPrevWeek DateTime
DECLARE @StartOfPrevWeek DateTime
--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)
SET @TodayDayOfWeek = datepart(dw, GetDate())
--get the last day of the previous week (last Sunday)
SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())
--get the first day of the previous week (the Monday before last)
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate())
INSERT INTO AAtblBankExport ( [Indicator], EntityID, GarnAmt, GarnIndicator, FI, Garnish, GarnDate )
SELECT IIf([Levy_txt]![PriEntityType]="002" Or [Levy_txt]![PriEntityType]="004","002","001") AS [Indicator], Levy_txt.PriEntityID, Sum([Levy_txt]![CaseBal]*0.01) AS GarnAmt, Levy_txt.LevyType, [Forms]![AAExport]![txtBankCode] AS FI, LevyTable.Garn, Date() AS GarnDate
FROM Levy_txt INNER JOIN LevyTable ON Levy_txt.PriEntityID = LevyTable.PriEntityID
WHERE (((LevyTable.GarnDate) Is Null) AND ((LevyTable.Exclude)=No)) AND ((LevyTable.ResultDate) Between CONVERT(VARCHAR, @StartOfPrevWeek,7) And CONVERT(VARCHAR, @EndOfPrevWeek+1,7))) 
GROUP BY IIf([Levy_txt]![PriEntityType]="002" Or [Levy_txt]![PriEntityType]="004","002","001"), Levy_txt.PriEntityID, Levy_txt.LevyType, LevyTable.Garn, Levy_txt.LevySrceID
HAVING (((LevyTable.Garn)=Yes) AND ((Levy_txt.LevySrceID)=[Forms]![AAExport]![txtBankFID]))
ORDER BY Levy_txt.PriEntityID;

As I have limited knowledge of SQL coding.

[weekend00]

datePart works fine for you.

...datepart("ww",[datefield])=datepart("ww",now)

add year if needed:
and year([datefield])=year(now)

[jgelpi16]

What exactly does the "datePart" function do? I am thought it determines what day of the week it is. I am not sure if this is going to work. What I need is...

Today is Tuesday. I need to find the Starting day of last week (Monday, September 13) and the ending day of last week (Friday, September 17). I want to then look for any fields in my table that have a date (probably just use the BETWEEN function). Since it could be any day of the week when the user kicks off this query I can't use a static calculation of Date() - 7 and Date() - 4.

[weekend00]

datepart("ww",now) returns the week number of today in this year, e.g. 39 for 9/21/2011.
if you want last week of current date, you can use:
...datepart("ww",[datefield])=datepart("ww",now)-1

[jgelpi16]

Excellent. Thank you.