[AccessD] Is there a way to determine months with 31 days that start on tuesday?

David McAfee davidmcafee at gmail.com
Wed Oct 28 14:23:15 CDT 2015


Thanks.

I ended up working on something similar to what you have below, but ended
up doing something like Paul suggested.

I've been looking at an Excel sheet that I made up some time ago, which
consists four sets  (31, 30, 29 and 28 day) of calendars each beginning
with a different day of the week, for a total of 28 calendars.

I transposed the data into four columns:
WeekDayNo, DateDayNo, WeekNo and MaxDays (all integers)

It ended up being 837 rows (only 5 of which are a "week5" number)

I wrote the following functions in VBA:
Public Function GetWeekNumberForDate(ByVal theDate As Date) As Integer
Dim WhereClause As String
    WhereClause = "DayNo=" & Weekday(theDate) & " AND DayOfMonth = " &
Day(theDate) & " AND MaxDaysOfMonth=" & Day(LastDayInMonth(theDate))
GetWeekNumberForDate = CInt(DLookup("WeekNo", "tblCalendarDays",
WhereClause))
End Function

Public Function LastDayInMonth(ByVal AnyDate As Date) As Date
 LastDayInMonth = DateAdd("m", 1, DateSerial(Year(AnyDate), Month(AnyDate),
1)) - 1
End Function

I then wrote a function going out three years and only three months come
up: 3/2016, 8/2017 & 5/2018

I'm going to use this function as it runs pretty quick, but at least I have
my 5 week months that I was looking for.

Thanks everyone!

David


More information about the AccessD mailing list