Good afternoon, I am working in Access 2016 and I am trying to build a query which will return a week number, starting at 1 between two dates.
So I do not want the standard or ISO Week number. If I choose a date range between 08/01/2018 and 02/28/2019, I want it to number from week 1 to week 30. No matter what start date I choose, I want to number from 1 to whatever.
I know that this: WeekNum: DatePart("ww",[Dates]) will give me the standard week number.
I have also tried this function that I found:
But that resets each month.... so I am getting results like this:
Date WeekNum WeekNo
8/1/2018 31 1
8/2/2018 31 1
8/7/2018 32 2
8/8/2018 32 2
.
.
.
9/4/2018 36 2
9/5/2018 36 2
What I want to see is:
Date WeekNum
8/1/2018 1
8/2/2018 1
8/7/2018 2
8/8/2018 2
.
.
.
9/4/2018 6
9/5/2018 6
Does that make sense... ? Can anyone offer any suggestions? Thank you.
So I do not want the standard or ISO Week number. If I choose a date range between 08/01/2018 and 02/28/2019, I want it to number from week 1 to week 30. No matter what start date I choose, I want to number from 1 to whatever.
I know that this: WeekNum: DatePart("ww",[Dates]) will give me the standard week number.
I have also tried this function that I found:
Code:
Public Function fCalcWeekOfMonth(dteDate As Date) As Byte
fCalcWeekOfMonth = DatePart("ww", dteDate) - _
DatePart("ww", DateSerial(Year(dteDate), Month(dteDate), 1)) + 1
End Function
But that resets each month.... so I am getting results like this:
Date WeekNum WeekNo
8/1/2018 31 1
8/2/2018 31 1
8/7/2018 32 2
8/8/2018 32 2
.
.
.
9/4/2018 36 2
9/5/2018 36 2
What I want to see is:
Date WeekNum
8/1/2018 1
8/2/2018 1
8/7/2018 2
8/8/2018 2
.
.
.
9/4/2018 6
9/5/2018 6
Does that make sense... ? Can anyone offer any suggestions? Thank you.