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?
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?
I have datepart('ww',[DateFilled]) gives me the week of the year; not month.
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.
You can try this
Code: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?You can try this
Code: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
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
oh ok.. gotcha.. i didnt realize modules worked like that.. it gave me what i wanted so thanks for the help.