Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2006
    Posts
    43

    Unanswered: Weekly Rolling Count Help

    Hi,

    Im trying to do a rolling count by week. Similar to the formula I have below.

    I would really appreciate any help I can get in converting the monthly formula below to weekly.

    *Also, if there is a way I can get the average Daily/Weekly/Monthly/Yearly

    Code:
    Reviewed10M: Sum(IIf([MyField] Between DateSerial(Year(Date()),Month(Date())-10,1) And DateSerial(Year(Date()),Month(Date())-9,0) And [MyField] Is Not Null,1,0))
    Thank you for your time and help

    Corey

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    I'm assuming you mean a count of the number of records made in the last week.

    The following query will count how many occurances appear in a list where the date is between now and a week ago...


    Code:
    SELECT Count(Table1.[FieldName]) AS CountOfFieldName
    FROM Table1
    WHERE (((Table1.[DateField]) Between DateAdd("ww",-1,Date()) And Date()));

  3. #3
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    Modified expression is given below for Weekly values:

    Reviewed10M: Sum(IIf(DatePart("ww",[MyField],vbSunday) Between datepart("ww",DateSerial(Year(Date()),Month(Date() )-10,1),vbSunday) And DatePart("ww",DateSerial(Year(Date()),Month(Date() )-9,0),vbSunday) And [MyField] Is Not Null,1,0))

    You can use yyyy instead of ww for Yearly count.
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  4. #4
    Join Date
    Nov 2006
    Posts
    43

    Weekly Rolling Count Help

    apr pillai abd christyxo,

    Thank you both soon much. This is what I was looking for. Is there something I need to do to get my database to see "vbSunday"? It dosnt seem to be working

  5. #5
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    If the vbSunday constant gives error then you may use it's equal value 1 for Sunday, 2 for monday....7 for saturday. This parameter is the start of the weekday. You can omit this parameter from the function, in that case 1 (sunday) will be taken as default value.
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •