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!
Code:
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.