lwildernorva
02-27-2007, 04:18 PM
Both a solution and two questions. On searching this site, I did not find a solution to an issue I had about recalculating dates in a letter that are calculated from an original date gained from user input. Further information about the project so that you have context for this code. I created a letter that requests information through several input boxes, including one for strHearingDate, the final date in the process. There are a number of other dates that I wanted to include in the letter that are calculated by working back from strHearingDate, which is always a weekday. These dates are then inserted into the letter by using various entries in the Bookmarks Range.
Since I solved this problem, I thought submitting the portion of the code that solved that problem might be helpful to someone else. I tried to give the post a title that would bring it up if someone did a search on the forum. Snippet is the operative term here; the following code is not the entire procedure for the letter I generated, but I believe is the relevant code necessary to solve this particular issue.
Dim strHearingDate As String
Dim strAWWDate As String
Dim FirstDate As Date
Dim IntervalType As String
Dim Number As Integer
strHearingDate = InputBox("Enter Hearing Date in Full Date Format, i.e., 'November 1, 2007'")
IntervalType = "d"
FirstDate = strHearingDate
Number = -30
strAWWDate = DateAdd(IntervalType, Number, strHearingDate)
While DatePart("w", strAWWDate, vbMonday) > 5
strAWWDate = DateAdd(IntervalType, -1, strAWWDate)
Wend
The Interval argument in the DateAdd and DatePart functions will accept a number of different interval arguments, including days ("d"), weeks ("ww"), weekdays ("w"), months ("m"), quarters ("q"), and years ("yyyy"). If I had needed to calculate forward rather than backward from strHearingDate or wanted to move the date that fell on the weekend to the following Monday, I could have used positive rather than negative numbers in DateAdd. The term vbMonday is an optional constant that sets the first day of the week for the calculation period to Monday and makes the "> 5" operative when the result returned is vbSaturday (6) or vbSunday (7). The default value for the first day of the week is vbSunday; under the default values, vbSunday is 1 while vbSaturday is 7. The change allows the simpler statement "> 5" rather than having to write code that searches for dates returned that are 1 or 7.
Although many of the dates are calculated by going back 14, 21, 28, and 56 days, all calculations that mean that the generated date will also be a weekday, several of the entries required going back 10, 15, and 30 days, which could result in a generated date that would fall on a weekend. I wanted to clean that issue up by finding a method that would take the generated date that fell on a weekend to the previous Friday. The While. . .Wend statement used above accomplishes that task.
Two quick questions: First, anyone have any alternatives that accomplish this purpose more efficiently? Second, am I correct in understanding that the only way to accomplish the additional goal of moving a generated date off all holidays in a year is by reference to an external database of annual holidays? I have no doubt that I can take care of annual holidays that occur on the same date (Christmas, New Year's Day, U.S. Veterans' Day, and U.S. Independence Day) without reference to an external database but the variable holidays (Presidents' Day, Labor Day, Memorial Day, Thanksgiving Day) apparently must be covered by reference to an external database (this information was gleaned from an MSDN article: http://msdn2.microsoft.com/en-us/library/aa227592(VS.60).aspx) (http://msdn2.microsoft.com/en-us/library/aa227592%28VS.60%29.aspx%29).
Since I solved this problem, I thought submitting the portion of the code that solved that problem might be helpful to someone else. I tried to give the post a title that would bring it up if someone did a search on the forum. Snippet is the operative term here; the following code is not the entire procedure for the letter I generated, but I believe is the relevant code necessary to solve this particular issue.
Dim strHearingDate As String
Dim strAWWDate As String
Dim FirstDate As Date
Dim IntervalType As String
Dim Number As Integer
strHearingDate = InputBox("Enter Hearing Date in Full Date Format, i.e., 'November 1, 2007'")
IntervalType = "d"
FirstDate = strHearingDate
Number = -30
strAWWDate = DateAdd(IntervalType, Number, strHearingDate)
While DatePart("w", strAWWDate, vbMonday) > 5
strAWWDate = DateAdd(IntervalType, -1, strAWWDate)
Wend
The Interval argument in the DateAdd and DatePart functions will accept a number of different interval arguments, including days ("d"), weeks ("ww"), weekdays ("w"), months ("m"), quarters ("q"), and years ("yyyy"). If I had needed to calculate forward rather than backward from strHearingDate or wanted to move the date that fell on the weekend to the following Monday, I could have used positive rather than negative numbers in DateAdd. The term vbMonday is an optional constant that sets the first day of the week for the calculation period to Monday and makes the "> 5" operative when the result returned is vbSaturday (6) or vbSunday (7). The default value for the first day of the week is vbSunday; under the default values, vbSunday is 1 while vbSaturday is 7. The change allows the simpler statement "> 5" rather than having to write code that searches for dates returned that are 1 or 7.
Although many of the dates are calculated by going back 14, 21, 28, and 56 days, all calculations that mean that the generated date will also be a weekday, several of the entries required going back 10, 15, and 30 days, which could result in a generated date that would fall on a weekend. I wanted to clean that issue up by finding a method that would take the generated date that fell on a weekend to the previous Friday. The While. . .Wend statement used above accomplishes that task.
Two quick questions: First, anyone have any alternatives that accomplish this purpose more efficiently? Second, am I correct in understanding that the only way to accomplish the additional goal of moving a generated date off all holidays in a year is by reference to an external database of annual holidays? I have no doubt that I can take care of annual holidays that occur on the same date (Christmas, New Year's Day, U.S. Veterans' Day, and U.S. Independence Day) without reference to an external database but the variable holidays (Presidents' Day, Labor Day, Memorial Day, Thanksgiving Day) apparently must be covered by reference to an external database (this information was gleaned from an MSDN article: http://msdn2.microsoft.com/en-us/library/aa227592(VS.60).aspx) (http://msdn2.microsoft.com/en-us/library/aa227592%28VS.60%29.aspx%29).