I need help writing a unique date function 1

[Sdaddy]

I am trying to write a function that will return the week number of a month. For instance Week 1, Week 2...I have tried writing an if statement, but I get a funky date in return. This is for a week over week view in MS Access on a report. I need the function or expression or conditional macro to make , for example 11/01/2002-11/02/2002, would be week 1", 11-03-11-09 would be "week 2" and so forth. I would use the on format macro(or anything that works I suppose. My last resort is to make a table and link it on the query level. That will introduce human error however. I greatly appreciate any help in this.

 

[asid]

Try using the DatePart function. Use the 'y' argument for the interval.

This returns the Day of Year.

Then, you could divide by 7 and discard the remainder to get the week number.

Try this test sub:

Sub test()
Dim test

test = DatePart("y", "7/11/2002&quot
Debug.Print "Week"; test \ 7
test = DatePart("y", "8/11/2002&quot
Debug.Print "Week"; test \ 7
test = DatePart("y", "14/11/2002&quot
Debug.Print "Week"; test \ 7
test = DatePart("y", "7/1/2002&quot
Debug.Print "Week"; test \ 7
test = DatePart("y", "30/12/2002&quot
Debug.Print "Week"; test \ 7
End Sub

I get this output:

Week 44
Week 44
Week 45
Week 1
Week 52

HTH

 

[VBslammer]

Try this:

Function WeekOfYear(ByVal datDate As Date) As Byte
  WeekOfYear = DatePart("ww", Date)
End Function

Sample call:

myWeek = WeekOfYear("2/23/2002")

VBSlammer

Unemployed in Houston, Texas

 

[VBslammer]

Oops, correction below:

Function WeekOfYear(ByVal datDate As Date) As Byte
  WeekOfYear = DatePart("ww",

datDate

)
End Function

VBSlammer

Unemployed in Houston, Texas

 

[VBslammer]

I re-read the post and I think this is more to your question:

Function WeekOfYear(ByVal datDate As Date) As String
  WeekOfYear = "Week " & DatePart("ww", datDate)
End Function

VBSlammer

Unemployed in Houston, Texas

 

[asid]

I can't believe I missed the ww argument!!!!

 

[Sdaddy]

I greatly appreciate your help. This is along the lines of what I am looking for... but I am looking for the week number as it relates to a particular month.. rather than to the year.

 

[VBslammer]

Oh, how about this:

Function WeekOfMonth(ByVal datDate As Date) As String
  WeekOfMonth = "Week " & DatePart("ww", datDate) - _
                          DatePart("ww", Month(datDate) & _
                          "/1/" & Year(datDate)) + 1
End Function

VBSlammer

Unemployed in Houston, Texas

 

[Sdaddy]

It works... your are the man(unless you are a woman)!!!!!

thanks!!!

One more challenge I'm having is that for the weeks that begin in one month but end in another, Is there a way to make those dates show up as for instance 10-27 to 10-31 as week 5, rather than week 1.


thanks again!!!