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)?
GrahamSkan

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?
_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.

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?

ASKER

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.

ASKER

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``````

ASKER

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

ASKER

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?

ASKER

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?

ASKER

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

ASKER

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
Rey Obrero (Capricorn1)

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

ASKER

You are BRILLIANT!!!!!!! It works! Thank you so much!

ASKER

Thank you so much! You are brilliant! Appreciate the time you put into it.