Query to give the number of weekdays in a given month

[Daniel]

How would I write a query to give me the number of Mondays, Tuesdays,
Wednesdays...etc. in a given Month and Year?

The Month/Year would be from a form to use in the criteria of the query.

Example: Month/Year, January/2006

Results: Sun = 5, Mon = 5, Tue = 5, Wed = 4, Thu = 4, Fri = 4, Sat = 4

Thanks...

 

[Tom Ellison]

Dear Daniel:

The classic solution is a table of "days off" which can include holidays as
well. Just a one column table. You can then use a query to COUNT() these.

A VBA module you can write can populate the table with all dates, excluding
weekends. You can then delete any holidays if you don't want to count
those.

Tom Ellison

 

[Le bucheron]

try this (quick and dirty way)

Public Function countday(dt_from, dt_to)
'this function return an array of 7 integer (1 to 7) counting the number
of monday, tuesday ... in a period of time
'1 is monday, 2 is tuesday .... 7 is sunday
Dim dt_i, dt_0, dt_1 As Date
dt_0 = CDate(dt_from)
dt_1 = CDate(dt_to)
Dim day_count(1 To 7) As Integer
For dt_i = dt_0 To dt_1
day_count(WeekDay(dt_i, vbMonday)) = day_count(WeekDay(dt_i,
vbMonday)) + 1
Next
countday = day_count()
End Function


Yves

 

[Michel Walsh]

Hi,



computing the number of weeks between the day before the first of the
month and the day before the first of the next month, specifying a week
start on the said weekday, should do. An example is probably easier to
understand:


? DateDiff("ww", #03-01-2006#-1, #04-01-2006#-1 , vbFriday)
5

? DateDiff("ww", #03-01-2006#-1, #04-01-2006# -1 , vbSaturday)
4

? DateDiff("ww", #03-01-2006#-1, #04-01-2006#-1, vbSunday)
4

? DateDiff("ww", #03-01-2006#-1, #04-01-2006#-1, vbWednesday)
5

? DateDiff("ww", #03-01-2006#-1, #04-01-2006#-1, vbTuesday)
4


Hoping it may help,
Vanderghast, Access MVP