# Thread: Weekly Rolling Count Help

1. Registered User
Join Date
Nov 2006
Posts
43

## Unanswered: Weekly Rolling Count Help

Hi,

I’m 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. Registered User
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. Registered User
Join Date
Jan 2009
Location
Kerala, India
Posts
188
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.

4. Registered User
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. Registered User
Join Date
Jan 2009
Location
Kerala, India
Posts
188
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.

#### Posting Permissions

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