Access Sum FUnction and IIF syntax

I have a report - in a textbox control source, I have the formula -

=Sum(IIf([WkBeginDate]>=Date()-28 And [WkBeginDate]<=Date()-1,[Sales],0))

What I would like to do is make it so that it gives totals from the 4 weeks prior to the Previous Sunday every time the report is run. Is there a way to format the IIF criteria to go back to the previous SUnday - then count back 4 weeks?
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Try this:

=Sum(IIf([WkBeginDate]>=DateAdd("d", -28, Date() - Weekday(Date()) + 1) And [WkBeginDate]<=Date()-1,[Sales],0))
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Here's the breakdown:

Date() - Weekday(Date()) + 1  <-- This gives your most recent previous Sunday
DateAdd("d", -28, <the above formula>)  <-- Then gives you four weeks before that.
0
CIOCommented:
You can use these expressions to find the first and last date:

and:
=DatePrevWeekday(Date(),1))

using this generic function:
``````Public Function DatePrevWeekday( _
ByVal datDate As Date, _
Optional ByVal bytWeekday As VbDayOfWeek = vbMonday) _
As Date

' Returns the date of the previous weekday, as spelled in vbXxxxday, prior to datDate.
' 2000-09-06. Cactus Data ApS.

' No special error handling.
On Error Resume Next

DatePrevWeekday = DateAdd("d", 1 - Weekday(datDate, bytWeekday), datDate)

End Function
``````
/gustav
0
Database Analyst / Application DeveloperAuthor Commented:
DSacker - I will give that a shot - and Gustav - I like the idea of doing it in VBA - but I have to check if that is an option with the final say. Thank you gentlemen - I shall return
0
Database Analyst / Application DeveloperAuthor Commented:
DSacker, It works so far - and no matter what day of the week the report is run, it will still start from the previous sunday?
0