Week Number of Month not Year?

[kwooten]

Is there anything i can put in the criteria of a query or VBA code that will provide me the week number of the month instead of the week number of the year?

[kwooten]

I have datepart('ww',[DateFilled]) gives me the week of the year; not month.

[pbaldy]

Sorry, I deleted that. I have a cheat sheet that said:

DatePart("w", dte)

would return the week of the month ("ww" for week of the year), but I tested and it doesn't appear to work. I'll poke around, but have to head to another office to deal with a PC issue.

[orange]

You can try this

Function WeekOfMonth(Mydate As Date) As Integer
'weeknumber of year based on your supplied Date - weeknumber of year of first of the month of the supplied Date
WeekOfMonth = DatePart("ww", Mydate) - DatePart("ww", DateSerial(Year(Mydate), Month(Mydate), 1)) + 1
End Function

[kwooten]

You can try this

Function WeekOfMonth(Mydate As Date) As Integer
'weeknumber of year based on your supplied Date - weeknumber of year of first of the month of the supplied Date
WeekOfMonth = DatePart("ww", Mydate) - DatePart("ww", DateSerial(Year(Mydate), Month(Mydate), 1)) + 1
End Function

Forgive my ignorance, but where do i put this?

[orange]

Create a Module;
Copy and paste this Function into the module; then save the module -- name it MyModule.
Then the function becomes available to you wherever you want to use it (Query, vba etc)

Then in a query, using your DateFilled, on your fields line use

WeekInvolved: WeekOfMonth(DateFilled)

When you run the query, you should see the week of the Month related to DateFilled

More info on modules and vba at
http://www.accessmvp.com/strive4peac...Chapter_02.pdf

[kwooten]

oh ok.. gotcha.. i didnt realize modules worked like that.. it gave me what i wanted so thanks for the help.