I need to count the number of each weekday in a month (# of Mondays, # of Tuesdays, etc.). Is this even possible in Access?
Let me see if I can explain the reason I need these 5 counts.
In our Operating Room, the time is allocated to different classes of surgeries (General, Neurology, Pediatrics, etc.), and we want to compare the actual minutes spent in each category with what was allocated, and the allocations are made per dayoftheweek.
Complicating the issue is that different amounts are allocated on different weekdays; for instance, General surgeries are allocated 450 minutes a day, except that it's allocated only 420 minutes on Mondays. ObGyn is allocated 450 minutes on Tuesdays and Wednesdays, and Plastics is allocated 240 minutes on Tuesdays and Fridays, and 450 minutes on Thursdays. You get the idea.
So I can calculate the correct "monthly" allocation only if I can figure out how many of each weekday occurs in a given month.
Any advice will be appreciated, even including "can't be done in Access". However, if that's the answer, I'll be most grateful for tips about how to accomplish this "outside" in such a way that I can use the results within Access.
Thanks!
A routine to determine the number of particular days of the week in a given month might go something like this :  Public Function NumWeekdays(ByVal datThis As Date, _

Optional ByVal intDay As Integer = vbMonday)

datThis = DateAdd("d", 1  Day(datThis), datThis)

intDay = (intDay Mod 7) + 1

intDay = Weekday(datThis, intDay)  1

NumWeekdays = DateDiff("d", datThis, DateAdd("m", 1, datThis))

NumWeekdays = (NumWeekdays + intDay) \ 7

End Function
It's not too easy to explain as it gets a bit fiddly, but if you look through it you might pick it up.
11 10527
First you calculate how many days it takes to get to the next Monday. Then you subtract that from the amount of days in the month. If that's less than 29, there's 4 Mondays in the month, otherwise it's 5 Mondays. The same goes for the other days of the week.
OOh, I like that! But let me make sure I understand how this is working: Wouldn't that "29" have to change depending on how many days there are in the month? I looked for a VBA function that would give me that number, but couldn't find it. Is there one? Or do I have to set up a table that just contains the number of days in each month (also thinking about leap years)?
NeoPa 32,184
Expert Mod 16PB
A routine to determine the number of particular days of the week in a given month might go something like this :  Public Function NumWeekdays(ByVal datThis As Date, _

Optional ByVal intDay As Integer = vbMonday)

datThis = DateAdd("d", 1  Day(datThis), datThis)

intDay = (intDay Mod 7) + 1

intDay = Weekday(datThis, intDay)  1

NumWeekdays = DateDiff("d", datThis, DateAdd("m", 1, datThis))

NumWeekdays = (NumWeekdays + intDay) \ 7

End Function
It's not too easy to explain as it gets a bit fiddly, but if you look through it you might pick it up.
@Rabbit, so what I'm going to do, I think, is calculate the number of days in the month by subtracting 1 day from the first day of the next month and extracting the 'day' from that calculated date.
However, it turns out that I don't know how to construct a date. I want to have a code something like:  Public Function Count_Weekdays(Month_Start As Date)

On Error GoTo Err_Count_Weekdays


Dim Next_Month

Dim Next_Month_s_Year

Dim Days_This_Month As Integer

Dim Days_To_Next As Integer

Dim Monday_Count As Integer


' Get number of days in the month

Next_Month_s_Year = DatePart(yyyy, Month_Start)

If DatePart(m, Month_Start) = 12 Then

Next_Month_s_Year = Next_Month_s_Year + 1

End If

'Next_Month = (1, datepart(m,Month_Start)+1, Next_Month_s_Year)

'Days_This_Month = day(Next_Month1)


' For each weekday:

' Calculate number of Days_To_Next Monday

' if Days_This_Month  Days_To_Next < Days_This_Month  1 then

' Monday_Count = 4


' Calculate minutes allocation for each Surgery type for this month

'(make an update query to update a table holding these values?)

but I don't really know how to write line 15. (and I'll worry about what follows after I get at least this part right!)
Oh, NeoPa, I might have known you'd just have this in your back pocket!
This is a function I use all the time that will calculate the number of days in a given month: 
Public Function DaysInMonth(Month, Year)

DaysInMonth = Day(DateSerial(Year, Month + 1, 0))

End Function

The dateSerial part is a little tricky, but I'll explain. The last day of the current month is equivalent to the day before the first day of next month. DateSerial(Year, Month+1, 0) will give us that day, and the day function extracts just the day portion (you could also use DatePart for the same thing, but I think this is cleaner.)
You could do something similar, using the Weekday (or DatePart) functions to figure out what day of week the first day of the month was, and that should give you the other piece you need to calculate the number of days using Rabbit's method.
Edit: I see the Bytes ninjas are in full force today. You anticipated my function before I posted it and NeoPa has a full answer already.
Wow, Gershwyn, this little function is a real eyeopener for meI didn't know the DateSerial function at all. Does this get the right year when Month is December? (I mean I assume it does, or you wouldn't use it "all the time"...)
Yes, it is smart enough to wrap around to the next year if needed. So if you specify a month of 12, it will calculate (Month + 1) = 13 and proceed with January, the same way it knows to treat a day of 0 as the previous day. It also handles leap years wonderfully, without having to code for the exceptions.
Test case: 
?DaysInMonth(1, 11)

31


?DaysInMonth(2, 11)

28


?DaysInMonth(12, 11)

31


?DaysInMonth(2, 12)

29

NeoPa 32,184
Expert Mod 16PB
It certainly does Sue. See the simple test below (run in the Immediate pane of the debugger).  ?CDate(DateSerial(2011,13,0))

31/12/2011
@Gershwyn: that's lovelyvery elegant. Okay, I'll try out these options and post back here which one I end up using.
Okay, I'm going with NeoPa's code for now (although I saved Gershwyn's off because I think I'll need it for another project later).
I now have more questions about how to accomplish my final goal of comparing "actual" with "allocated" minutes, but I'll ask those in other threads.
Thanks, everyone!
Post your reply Sign in to post your reply or Sign up for a free account.
Similar topics
1 post
views
Thread by josepe 
last post: by

7 posts
views
Thread by developer 
last post: by

5 posts
views
Thread by Ali Baba 
last post: by

10 posts
views
Thread by Bill 
last post: by
                