Sequential week numbering between dates (1 Viewer)

Indigo

Registered User.
Local time
Today, 13:14
Joined
Nov 12, 2008
Messages
241
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:

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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:44
Joined
Sep 21, 2011
Messages
14,044
Wouldn't you need to find the 'real' week number for the date and then subtract that number less 1 from the 'real' week number.?

With the caveat of adjusting for another year as well.
 

Indigo

Registered User.
Local time
Today, 13:14
Joined
Nov 12, 2008
Messages
241
Not sure how I would adjust when the calendar year changes?
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:44
Joined
Sep 21, 2011
Messages
14,044
No, as you would calculate the number of years difference, 1 year would be an additional 52 weeks, 2 years would be 104 weeks.

You would need your own function.
 

plog

Banishment Pending
Local time
Today, 10:44
Joined
May 11, 2011
Messages
11,611
First, this is not going to involve getting the week value from the system. You will want to use DateDiff(https://www.techonthenet.com/access/functions/date/datediff.php) and divide by 7.

That might be all you need, but you haven't given enough information. Obviously whatever you enter for Start DAte is week 1, but is it week 1 day 1? Or perhaps will your weeks always start on Sunday, which means if someone enters 9/6/2018 for the Start Date that means week 2 starts on 9/9/2018?

If that's the case you will need to use the WeekDay (https://www.techonthenet.com/access/functions/date/weekday.php) function to track back your weeks to the correct one.
 

Indigo

Registered User.
Local time
Today, 13:14
Joined
Nov 12, 2008
Messages
241
Thank you, plog, I have looked at those posts. I don't want to count the number of weeks as the DateDiff function will do...

=Int(DateDiff("d", #8/1/2018#, #3/4/2019#) / 7)

will give me 30 weeks

...and the start date will always be the first of the month. So regardless if the first if the month is a Sunday or a Wednesday, I want that week to count as week 1, then the next week (starting on Sunday) as week 2 and so on.
 

plog

Banishment Pending
Local time
Today, 10:44
Joined
May 11, 2011
Messages
11,611
I am lost as to what you actually want. I know you want a query, which returns data, so give me some sample data. Demonstrate with data what you want.
 

Indigo

Registered User.
Local time
Today, 13:14
Joined
Nov 12, 2008
Messages
241
I created a field on a form called "WeekCount" that counts the number of weeks in my date range using DateDiff and then added this field to my query....

Code:
WKNo: IIf(DatePart("ww",[Dates])>[Forms]![frmScheduleViewNew]![WeekCount],DatePart("ww",[Dates])-[Forms]![frmScheduleViewNew]![WeekCount],DatePart("ww",....

But don't know what to do for the "Falsepart" of the IIF statement to continue counting into a new year.... :-(
 

Indigo

Registered User.
Local time
Today, 13:14
Joined
Nov 12, 2008
Messages
241
Scrap that... that IIF statement won't work.... sigh :banghead:

I am trying to build a schedule... I have calculated the working days for each month and I need to determine the week number corresponding to the date, BUT the week number for the first week of the schedule needs to be 1 and not the standard calendar week number. So if my schedule starts on August 1st, I want the week number to return the number 1, not 31. If the schedule starts September 1st, I want the schedule to start with week number 1 and not 36.
 
Last edited:

MarkK

bit cruncher
Local time
Today, 08:44
Joined
Mar 17, 2004
Messages
8,178
It seems to me that week number should be easily calculable by dividing elapsed days by seven. I can successfully calculate this in a query as follows...
Code:
ElapsedWeeks: Int((Date()-[Created])/7)
That query has a field called Created, which is a date. and this shows elapsed weeks until today. To calculate elapsed weeks for a date provided as a parameter, you could do...
Code:
ElapsedWeeks: Int(([prmStartDate]-[YourDateField])/7)
No?
Mark
 
Last edited:

Indigo

Registered User.
Local time
Today, 13:14
Joined
Nov 12, 2008
Messages
241
hmm... getting decimals with that one, Mark....since some months start mid-week...?
 

MarkK

bit cruncher
Local time
Today, 08:44
Joined
Mar 17, 2004
Messages
8,178
Maybe show your exact code. The Int() function in the example I posted should remove everything to the right of the decimal point. You can also use Round(), or CInt() or CLng(), but yeah, you need something to drop the fractional part.
Mark
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:44
Joined
Jan 23, 2006
Messages
15,364
Indigo,

Show us the code you ran. The examples Markk provided had the arithmetic inside an INT() function and should not have decimal places in the result.
 

JHB

Have been here a while
Local time
Today, 16:44
Joined
Jun 17, 2012
Messages
7,732
I would use a table for holding the weeknumbers, the week's start- and week's enddate, (created on the fly).
Sample database attached.
 

Attachments

  • WeekNumbers.accdb
    468 KB · Views: 93

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:44
Joined
May 7, 2009
Messages
19,169
copy this in a module:
Code:
Public Function fncWeekNum(dt As Variant) As Variant
    Dim thisWeek As Integer
    If dt & "" = "" Then Exit Function
    dt = CDate(dt)
    thisWeek = DatePart("ww", DateSerial(Year(dt), Month(dt), 1))
    fncWeekNum = DatePart("ww", dt) - thisWeek + 1
End Function

to call in Query:

select dateField, fncWeekNum(dateField) As WeekNum From yourTable;


as control source of textbox:

=fncWeekNum([dateTextBox])
 

Indigo

Registered User.
Local time
Today, 13:14
Joined
Nov 12, 2008
Messages
241
Just getting back to this after a week... Thank you, Arnel, but that function is giving me the same results as the function I posted at the beginning of this thread... in that it is resetting the week number with each new month.
 

plog

Banishment Pending
Local time
Today, 10:44
Joined
May 11, 2011
Messages
11,611
Sample data please. Show us what you want via example.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:44
Joined
May 7, 2009
Messages
19,169
here is the modified one.
you pass on the first parameter the start of week 1 date, on your first post
it is 8/1/2018.
every time you call this function you pass that same date.

the second parameter is the date you want to return which week number (based on your #1 post):

fncWeekNum(#8/1/2018#, #8/1/2018#) = week 1
...
...
fncWeekNum(#8/1/2018#, #9/4/2018#) = week 6
Code:
Public Function fncWeekNum(dtStartOfWeekOne As Date, Optional dtTheDate As Variant = Null) As Variant
    Dim thisWeek As Integer
    Dim Gap As Integer
    
    If dtTheDate & "" = "" Then _
        dtTheDate = Date
        
    '* get the difference in days
    Gap = DateDiff("d", DateSerial(Year(dtStartOfWeekOne), 1, 1), dtStartOfWeekOne) - 1
    fncWeekNum = DatePart("ww", DateAdd("d", -Gap, dtTheDate))
End Function
 

Users who are viewing this thread

Top Bottom