Week beginning . . .

[Guest]

Hi

I need to develop a database that outputs a monthly Statement where the
first column should be "Week Beginning" and show the dates of all the Mondays
in that particular month. ie. the Statement should look something like:

Week
Beginning Child Hrs Fee Payment Balance
06/03/2006 Sarah 12 $29 $0.00 $348
13/03/2006 Sarah 12 $29 $0.00 $696
20/03/2006 Sarah 12 $29 $0.00 $1044
27/03/2006 Sarah 12 $29 $0.00 $1352

Is there any way to calculate the date of all the Mondays in a particular
month, or will I need to fill a table with this information??

 

[nanne]

instead using Mondays, you could use weeknumbers 1- 52

 

[Ken Snell \(MVP\)]

Here is a function that I've written to get the specific date of a month,
such as you seek:

Function GetActualDateForGenericDayOfMonth(lngYear As Long, _
lngMonthNumber As Long, lngWeekDayNumber As Long, _
lngWeekDayOccurrenceOfMonth As Long) As Date
' Ken Snell - July 1, 2004
'***THIS FUNCTION RETURNS THE ACTUAL DATE FOR A GENERIC
'***DAY OF A MONTH (e.g., THE THIRD MONDAY OF A MONTH).
'***THE VARIABLES PROVIDED TO THE FUNCTION:
'*** lngYear - the year for the date.
'*** lngMonthNumber - the number of the month for the date
'*** (e.g., January = 1, February = 2, etc.).
'*** lngWeekDayNumber - the number of the weekday for the date
'*** (e.g., Sunday = 1, Monday = 2, etc.).
'*** lngWeekDayOccurrenceOfMonth - the order number of the weekday for
the date
'*** (e.g., 1 = first occurrence of that weekday in the month,
'*** 2 = second occurrence of that weekday in the month, etc.).
'***IF AN ERROR OCCURS DURING THE EVALUATION OF THE DATE,
'***A VALUE OF ZERO IS RETURNED BY THE FUNCTION.

On Error Resume Next
GetActualDateForGenericDayOfMonth = DateSerial(lngYear, lngMonthNumber, _
8 - DatePart("w", DateSerial(lngYear, lngMonthNumber, 1), _
1 + lngWeekDayNumber Mod 7) + (lngWeekDayOccurrenceOfMonth - 1) * 7)
If Err.Number <> 0 Then GetActualDateForGenericDayOfMonth = 0
Err.Clear
End Function

 

[Guest]

Hi Ken

Sorry, I have been away for while. Thank you for your function, it worked
perfectly!!


--
Regards
Winsa

Here is a function that I've written to get the specific date of a month,
such as you seek:

Function GetActualDateForGenericDayOfMonth(lngYear As Long, _
lngMonthNumber As Long, lngWeekDayNumber As Long, _
lngWeekDayOccurrenceOfMonth As Long) As Date
' Ken Snell - July 1, 2004
'***THIS FUNCTION RETURNS THE ACTUAL DATE FOR A GENERIC
'***DAY OF A MONTH (e.g., THE THIRD MONDAY OF A MONTH).
'***THE VARIABLES PROVIDED TO THE FUNCTION:
'*** lngYear - the year for the date.
'*** lngMonthNumber - the number of the month for the date
'*** (e.g., January = 1, February = 2, etc.).
'*** lngWeekDayNumber - the number of the weekday for the date
'*** (e.g., Sunday = 1, Monday = 2, etc.).
'*** lngWeekDayOccurrenceOfMonth - the order number of the weekday for
the date
'*** (e.g., 1 = first occurrence of that weekday in the month,
'*** 2 = second occurrence of that weekday in the month, etc.).
'***IF AN ERROR OCCURS DURING THE EVALUATION OF THE DATE,
'***A VALUE OF ZERO IS RETURNED BY THE FUNCTION.

On Error Resume Next
GetActualDateForGenericDayOfMonth = DateSerial(lngYear, lngMonthNumber, _
8 - DatePart("w", DateSerial(lngYear, lngMonthNumber, 1), _
1 + lngWeekDayNumber Mod 7) + (lngWeekDayOccurrenceOfMonth - 1) * 7)
If Err.Number <> 0 Then GetActualDateForGenericDayOfMonth = 0
Err.Clear
End Function