To calculate Day Count (Monday,Tuesday etc) in month in MS Access

[Guest]

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]

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Randy Harris]

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