# 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?

Try this:

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

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

Yes. It subtracts the weekday, then adds 1.

So tomorrow (Tuesday) will be weekday 3. Subtracted will = Saturday. Plus 1 will = Sunday.

:)
0

Database Analyst / Application DeveloperAuthor Commented:
Awesome as always
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.