Determining Dates between 2 dates

[kjpreston]

Hello! I am in something of a quandary. I have been tasked with determining which reports are due each month and the date they are due. Now, some of the reports require monthly reporting, some quarterly, some annual, a few semiannual, and even at least one that is biennial. To complicate matters further, most of the monthly reports are due on the 5th business day of the month, (skipping weekends and holidays, I have a table with the holiday dates already in it) others are due on a specific date each month say the 10th. However if the 10th falls during the weekend it will be due the following Monday, so if the 10th is on Saturday the report is due on the 12th.
Just to add another layer of fun to all this, my boss wants any annual reports to begin showing up on the list 2 month prior to the due date, as calculated above. So if the report is due January 1 of each year it needs to start showing up on the list in November. I have the first occurrence and the final occurrence dates of each report, the number of calendar days or business days as required.
I have started some code that was supposed to give me the next occurrence of the reports based on the first occurrence, but all it returns is the last record, I need all of them. I was hoping I could modify this to give me each occurrence of the reports, so all I would have to do is change the first occurrence date if necessary to update the list. This code only returns the date per calendar days at the moment, I haven’t incorporated the business days, weekends and holidays, I have the code for that, but I figure the rest can be added when I get this working.
Thanks for your time, kjpreston

Public Function FindDates() As String

Dim x As String
Dim y As String
Dim z As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("DRDListing", dbOpenDynaset)

rs.MoveFirst
Do Until rs.EOF 'Or Not IsNull(rs("DRD"))
x = rs!DRD.Value & " " & rs!Title.Value
y = DateSerial(Year(rs!InitialPeriod), Month(rs!InitialPeriod) + 1, 1)
z = DateAdd("d", rs!StartDat, y)

rs.MoveNext

Loop

rs.Close
Set rs = Nothing

FindDates = x & " " & z
Debug.Print
End Function


Code for skipping weekends and holidays

Public Function SkipWendHol(sd As Date, i As Integer) As Date
Dim nd As Date
K = 0
j = 0
If Not i = 0 Then
Do While Not j = i
If i < 0 Then
j = j - 1
Else
j = j + 1
End If
nd = FormatDateTime(DateAdd("d", j, sd), vbShortDate)
If Weekday(nd, vbMonday) = 6 Or Weekday(nd, vbMonday) = 7 Then
If i < 0 Then
i = i - 1
Else
i = i + 1
End If
End If
If Not IsNull(DLookup("[HDate]", "[TTempHol]", "[HDate]=#" & nd & "#")) And Not (Weekday(nd, vbMonday) = 6 Or Weekday(nd, vbMonday) = 7) Then
If i < 0 Then
i = i - 1
Else
i = i + 1
End If
End If
Loop
SkipWendHol = nd
Else
SkipWendHol = sd
End If
End Function


Code for determining business days
Public Function busydays(b As Date, e As Date) As Integer
Dim x As Integer
Dim y As Integer
Dim w As Date
Dim h As Date
w = DateAdd("d", 2, e)
h = DateAdd("d", -1, e)
y = DateDiff("d", b, DateAdd("d", 1, e))
x = DateDiff("ww", b, e, vbSunday)
x = x + DateDiff("ww", b, e, vbSaturday)
x = x + DCount("[HDate]", "[Holiday]", "[Hdate] > #" & b & "# and [Hdate] < #" & w & "#")
busydays = y - x
End Function

 

[smedvid]

Just a suggestion...

As opposed to developing semi-complex logic to identify next report date based on current date or last run date; why not create a report table that contains report name (of rather Report ID), and due dates, last print date, user id, etc. I have done something similar in the past, even with a manual override if needed. This allowed historical reports to be run if necessary.

In my case, I only had perhaps 5 or 6 reports. My feeling was, what if the reporting time period changed, new reports were added or some reports were deleted. A table driven approach to manage timelines seemed approriate in my case, considering I only had a handful of reports and this offered the greatest flexibility if changes needed to made quickly without coding. I decided to prefill my report timeline table with reports and dates through 2014... So far, it has worked out perfectly...

Good Luck... htwh,





Steve Medvid
IT Consultant & Web Master

 

[kjpreston]

I have about 40 reports to deal with. I have a table with ID, title, initial report date, final report date, etc.. I probable could include a last submitted date which I have in a separate table. I was planning on using a pop up to enter the dates for the month needed and have the code fill a temporary table and give me the list for that month without having to store each report at least 5 times in a table for annual reports or 72 times for monthly reports over the 5 year span of the contract. That way past or future months could be run based on the dates entered in the pop up. I have a second version of the FindDates()code I posted earlier that allows me to update a table with the results, but that also only returns the last record.

 

[MichaelRed]

consider a "dates" table with the successive dates into the future (five to ten years?) fill the table with the date, day of the month, day of the year, name of the day, "isHoliday", bussiness day of the week, month and year (seperate fields).

A simple reference to the date can then retrieve any / all of the included information.

A seperate table of report critreria along with a join to the dates table can easily report what reports are due on any give date or whenever any given report is due.

Most of fields of the dates table can be filled in quite easily via code. My personal experience has led ne to seperate the holidays in a seperate table, as I find even within a single company (or even department) these will vary from time to time and their seperation eases the maintenance.




MichaelRed

 

[kjpreston]

I was hoping not to have to do something like that, but it seems that is the answer from other sources as well. it does seem to defeat the purpose though. Thanks for the help I do appreciate it.