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?
dawber39Database Analyst / Application DeveloperAsked:
Who is Participating?
 
dsackerContract ERP Admin/ConsultantCommented:
Try this:

=Sum(IIf([WkBeginDate]>=DateAdd("d", -28, Date() - Weekday(Date()) + 1) And [WkBeginDate]<=Date()-1,[Sales],0))
0
 
dsackerContract ERP Admin/ConsultantCommented:
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
 
Gustav BrockCIOCommented:
You can use these expressions to find the first and last date:

    =DateAdd("ww", -4, DatePrevWeekday(Date(),1))
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

Open in new window

/gustav
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dawber39Database 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
 
dawber39Database 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
 
dsackerContract ERP Admin/ConsultantCommented:
Yes. It subtracts the weekday, then adds 1.

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

:)
0
 
dawber39Database 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.

All Courses

From novice to tech pro — start learning today.