Link to home
Start Free TrialLog in
Avatar of Anneline
AnnelineFlag for Hong Kong

asked on

Week Number in a specific month

How do I calculate the the week number in that specific month (eg. 2 June 2008 = 1)?
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

If your week starts with the first of the month then

weekno = int(dayno-1)/7 + 1

Or do you want to start on a particular day?
Avatar of _Nopik_
_Nopik_

In MySQL:
select week(now())-week(now()-interval (day(now())-1)day)+1;
I'm not sure how it will be in your SQL, consult user manual for date/time arithmetic.
Avatar of Anneline

ASKER

For example July 2008 - the 5th is still week 1, but the 6th is week 2. The 1st of July didn't start on the first weekday. Make sense?
Hi Nopik, i tried using what you sent and made some changes but i can't get it to work.
Just as I wrote in previous answer, just compare year week numbers of current date and 1st of month.
Do you get some errors or just the result is wrong? If you get error, please post it, if result is wrong, please provide example data and their result.
I can't even get it to run - i keep having errors in terms of format, syntax etc. I don't know what the SQL equivalent would be to the MySQL you gave.
place this function in a module


Function getWeekNumMon(d As Date) As Integer
Dim d1 As Date,d2 As Date, sunArr(), j, sunDate As Date
d1 = DateSerial(Year(d), Month(d), 1)
d2 = DateSerial(Year(d), Month(d) + 1, 0)
Select Case Weekday(d1)
    Case 1 'Sunday
        sunDate = d1: j = 1
        Do Until sunDate >= d2
            ReDim Preserve sunArr(j)
            sunArr(j) = sunDate
            sunDate = sunDate + 7: j = j + 1
        Loop
        
    Case 2 To 7
        sunDate = d1 + 8 - (Weekday(d1)): j = 1
        Do Until sunDate >= d2
            ReDim Preserve sunArr(j)
            sunArr(j) = sunDate
            sunDate = sunDate + 7: j = j + 1
        Loop
        
End Select
For j = 1 To UBound(sunArr)
    If d = sunArr(j) Then
        getWeekNumMon = j
        Exit For
        
    ElseIf d > sunArr(j) And d < sunArr(j + 1) Then
        getWeekNumMon = j
        Exit For
    End If
 
Next
End Function

Open in new window

Thanks Capricorn. I tried calling this procedure in a form - it returns the value of 1 for all the weeks? What am I doing incorrectly?
I created a continuos form with all the records and their varying dates. Added a text field that called the procedure on Form Open.
you have to pass the date

getWeekNumMon(#6/2/2008#)

will give you 1

getWeekNumMon(#6/27/2008#)

will return 4
That's not going to work for me as it's contained in a Date field with variable dates. Is there not a way to have it calc: getWeekNumMon([DateEntered])
Alternatively, how do I get that to work on a Date field without having to have type in a fixed date each time?
K, i got it to half work. I made the Control Source for the WeekNumber field the following: =getWeekNumMon([DateEntered]). It calculated some of them and then gave the following error for the others: "Subscript out of range"

Highlighting this line - 3rd or so from the bottom.

    ElseIf d > sunArr(j) And d < sunArr(j + 1) Then

the (#6/27/2008#) is just an example, you can pass to the function a field name

getWeekNumMon([DateField])
what is the date value when you got the error?
There are a number of dates and they're not always the same dates - this time when i loaded the form, i had a 70% success rate. Runtime error 9 keeps popping up though.
Anneline,
can you attach your db. check attach File below
Sure, i've simplified it so that just the objects you need are in the database. I'm working with the TB table. I saved it as an .MDB (2003 format) file although its a .ACCDB file (2007). Same error though either way.
Experts-Exchange-Query.zip
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You are BRILLIANT!!!!!!! It works! Thank you so much!
Thank you so much! You are brilliant! Appreciate the time you put into it.