Set date to week of the month

[Guest]

I am trying to creat a button on a form that will pull data according to the
week of the month. I want it to count week 1, 2, 3, 4, 5 (if there are 5
weeks within a certain month) and then start over with week 1 the next month.
The new month will always begin on the first Monday. Example: 10/3 - 10/9
would be week 1, 10/10 - 10/16 would be week 2, 10/17 - 10/23 would be week
3, 10/24 - 10/30 would be week 4 and 10/31 - 11/6 would be week 5. The new
month would begin on 11/7 as week 1. Is there a way to assign weeks in
Access? Thanks!

 

[Guest]

Use a query to buils a list of Mondays. If Monday is a holiday then you will
need to add a No Sale date record.

Query named SalesWeek --
SELECT Weekday([SalesDate]) AS x, Format([SalesDate],"mm/dd") AS SalesWeek
FROM Sales
WHERE (((Weekday([SalesDate]))=2));

Total Sales for Week --
SELECT SalesWeek.SalesWeek, Sum(Sales.QTY) AS SumOfQTY
FROM Sales, SalesWeek
WHERE (((DateDiff("d",[SalesDate],[SalesWeek])) Between 1 And 7))
GROUP BY SalesWeek.SalesWeek;

 

[Wayne Morgan]

You will need to create you own function to calculate this. You could then
pass the current date to that function and have the function return the week
number.

I did a few quick tests, but I think this will do it.

Public Function WeekOfMonth2(dteDate As Date) As Integer
'Count Monday as the first day of the week.
'Days in the month before the first Monday
'are treated as belonging to the last week
'of the previous month.
Dim intWeekDay As Integer, intDay As Integer
Dim i As Integer, intFirstMonday As Integer
intWeekDay = Weekday(dteDate)
intDay = Day(dteDate)
'If the day of the month is before the first Monday
'Then get the week of the last day of the previous month
If intWeekDay <> 2 And intDay < 7 Then
WeekOfMonth2 = WeekOfMonth2(DateSerial(Year(dteDate), Month(dteDate),
0))
Else
'Find the first Monday
For i = 1 To 7
If Weekday(DateSerial(Year(dteDate), Month(dteDate), i)) = 2 Then
intFirstMonday = i
Exit For
End If
Next i
WeekOfMonth2 = ((intDay - intFirstMonday) \ 7) + 1
End If
End Function

 

[Guest]

This code worked fine but now I have a change to make. If the first week of
the month does not fall on a monday I still want that week to be week 1 and
then each monday after that will be an additional week. For example.....in
November the 1st is on a tuesday, I want that to be week 1 and then on monday
11/7 to be week 2, etc. How do I change the code to do that?

 

[Wayne Morgan]

This one is a little simpler.

Public Function WeekOfMonth(dteInputDate As Date) As Integer
Dim intDate As Integer
intDate = Day(dteInputDate)
WeekOfMonth = (intDate \ 7) + 1 + (intDate Mod 7 = 0)
End Function

 

[James A. Fortune]

This one is a little simpler.

Public Function WeekOfMonth(dteInputDate As Date) As Integer
Dim intDate As Integer
intDate = Day(dteInputDate)
WeekOfMonth = (intDate \ 7) + 1 + (intDate Mod 7 = 0)
End Function

Here's a more general version:

Public Function WeekOfMonth(dteInputDate As Date, intStartWeekday As
Integer) As Integer
Dim intWeekdayOf1st As Integer
Dim intDaysInWeek1 As Integer
Dim intDaysPastWeek1 As Integer

intWeekdayOf1st = WeekDay(DateSerial(Year(dteInputDate),
Month(dteInputDate), 1))
intDaysInWeek1 = (6 + intStartWeekday - intWeekdayOf1st) Mod 7 + 1
If Day(dteInputDate) <= intDaysInWeek1 Then
WeekOfMonth = 1
Else
intDaysPastWeek1 = Day(dteInputDate) - intDaysInWeek1
WeekOfMonth = 1 + (intDaysPastWeek1 \ 7) + Abs(intDaysPastWeek1 Mod 7
<> 0)
End If
End Function


Note: This function can be shortened slightly by using the Ceiling(X) =
- Int(-X) function posted by Van T. Dinh:
WeekOfMonth = 1 - Int(-intDaysPastWeek1 / 7)
When used in a query, use the weekday numbers (Sunday = 1, ..., Saturday
= 7) directly instead of vbSunday, ..., vbSaturday. It counts the days
during the month before the first starting weekday, if any, as week 1.

Also, see the technique used in:

http://groups.google.com/group/microsoft.public.access/msg/c777af0e1fa201c4

Note: The Ceiling function can similarly simplify the expression used there.

James A. Fortune