I want to calculate in every month how many days (like Mondays, Tuesdays etc)
are falling. Basically I have two group of People (Monday, Wednesday, Friday)
and (Tuesday, Thrusday, Saturday). And want to calculate number of days
possible for each group in a month. It varies from 12 to 14 depends on
starting and ending Day of month.
Vincent Johns - 27 Dec 2005 23:14 GMT
> I want to calculate in every month how many days (like Mondays, Tuesdays etc)
> are falling. Basically I have two group of People (Monday, Wednesday, Friday)
> and (Tuesday, Thrusday, Saturday). And want to calculate number of days
> possible for each group in a month. It varies from 12 to 14 depends on
> starting and ending Day of month.
If that's all you want to do, I suggest using Excel. Fill a List
(Excel's version of a Table) with days of the year from 1 to 365,
convert to dates, and sort based on the day of the week.
In Access, you could do something similar (use a Table with all those
days), but I think it would take more work. The Excel version you could
probably finish in 5 or 10 minutes.
-- Vincent Johns <vjohns@alumni.caltech.edu>
Please feel free to quote anything I say here.
Randy Harris - 28 Dec 2005 00:01 GMT
There are a lot of different ways of doing that. You didn't indicate how
you wanted to use the information. You posted in queries, but I can't think
of a way to do it in a query without a function of some sort. Here are a
pair of functions that compute the number of days for a given month.
Public Function MWFcount( _
ByVal yNum As Integer, _
ByVal mNum As Integer _
) As Integer
Dim I As Integer, Counter As Integer
For I = 1 To Day(DateSerial(yNum, mNum + 1, 0))
Select Case Weekday(DateSerial(yNum, mNum, I))
Case 2, 4, 6: Counter = Counter + 1
End Select
Next I
MWFcount = Counter
End Function
Private Function TThScount( _
ByVal yNum As Integer, _
ByVal mNum As Integer _
) As Integer
Dim I As Integer, Counter As Integer
For I = 1 To Day(DateSerial(yNum, mNum + 1, 0))
Select Case Weekday(DateSerial(yNum, mNum, I))
Case 3, 5, 7: Counter = Counter + 1
End Select
Next I
TThScount = Counter
End Function
Both take the year and month as input arguments. The first returns the
number of Mondays, Wednesdays and Fridays in that month. The second returns
the number of Tuesdays, Thursdays and Saturdays. You could use them in a
query that grouped by months.
HTH

Signature
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
> I want to calculate in every month how many days (like Mondays, Tuesdays etc)
> are falling. Basically I have two group of People (Monday, Wednesday, Friday)
> and (Tuesday, Thrusday, Saturday). And want to calculate number of days
> possible for each group in a month. It varies from 12 to 14 depends on
> starting and ending Day of month.