Calculating Dates Query 1

[iuianj07]

Hello guys,

I have a MAXDate query, that gives the MAX Date between 3 different dates, for me to achieve this I have a module:

Public Function Maximum(ParamArray MyArray()) As Variant

   Dim intLoop As Long

   Maximum = Null
   For intLoop = LBound(MyArray) To UBound(MyArray)

       If IsNull(MyArray(intLoop)) Then
           'do nothing
       ElseIf IsNull(Maximum) Then
           Maximum = MyArray(intLoop)
       ElseIf MyArray(intLoop) > Maximum Then
           Maximum = MyArray(intLoop)
       End If

   Next

End Function

Then on the MaxDate query:

SELECT Job_Tracking.ReportingPeriod, Job_Tracking.LoanNumber, JobTracking_PropertyCount.CountOfLoanNumber AS [Property Count (<>SUM)], Job_Tracking.DateAssigned, Job_Tracking.RushReqDate, Job_Tracking.ResolvedIssueDate, Maximum([DateAssigned],[RushReqDate],[ResolvedIssueDate]) AS MaxDate
FROM Job_Tracking LEFT JOIN JobTracking_PropertyCount ON (Job_Tracking.LoanNumber = JobTracking_PropertyCount.LoanNumber) AND (Job_Tracking.ReportingPeriod = JobTracking_PropertyCount.ReportingPeriod)
GROUP BY Job_Tracking.ReportingPeriod, Job_Tracking.LoanNumber, JobTracking_PropertyCount.CountOfLoanNumber, Job_Tracking.DateAssigned, Job_Tracking.RushReqDate, Job_Tracking.ResolvedIssueDate;

My main problem though is, if for example I want to add another column, let's say DueDate2: [MaxDate]+1 it gives an error.

Any idea why it doesn't calculate the date? is it because MaxDate is a function?

Thanks

 

[SkipVought]

Hi,

How about...

DueDate2: [ResolvedIssueDate]+1

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[iuianj07]

Hello Skip,

yes, If I used any of the 3 dates, it is calculating correctly. but when I use the MaxDate then that's the time it gives an error.

Thanks

 

[MajP]

Maximum([DateAssigned],[RushReqDate],[ResolvedIssueDate]) + 1 as DueDate

 

[iuianj07]

hello MajP,

this works, however I can't hardcode the "+1" and I only used it as an example. What we really have is another query that has the column to really determine how many days we need to add to MaxDate, the query is:

SELECT MAXDate.ReportingPeriod, MAXDate.LoanNumber, MAXDate.[Property Count (<>SUM)], MAXDate.MaxDate, IIf([TATRush] Is Null,[TATDays],[TATRush]) AS TotalTATTime, [MaxDate]+[TotalTATTime] AS DueDate
FROM (MAXDate LEFT JOIN TATDays ON (MAXDate.MAXDateYear = TATDays.Year) AND (MAXDate.MAXDateMonth = TATDays.Month)) LEFT JOIN TATRush ON (MAXDate.LoanNumber = TATRush.LoanNumber) AND (MAXDate.ReportingPeriod = TATRush.ReportingPeriod)
GROUP BY MAXDate.ReportingPeriod, MAXDate.LoanNumber, MAXDate.[Property Count (<>SUM)], MAXDate.MaxDate, IIf([TATRush] Is Null,[TATDays],[TATRush]), [MaxDate]+[TotalTATTime], TATRush.TATRush, TATDays.TATDays;

Based on the above query, what I want to achive is MaxDate + TotalTATTime


Thank you MajP

 

[SkipVought]

oops, I missed some essential stuff!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[iuianj07]

okay, I kinda got it to work (thanks to your suggestion MajP), here is the query (there will be more fields on this one since earlier I removed some of it):

SELECT MAXDate.ReportingPeriod, MAXDate.LoanNumber, MAXDate.[Property Count (<>SUM)], MAXDate.MaxDate, IIf([TATRush] Is Null,[TATDays],[TATRush]) AS TotalTATTime, Maximum([MaxDate].[DateAssigned],[RushReqDate],[ResolvedIssueDate])+[TotalTATTime] AS DueDate, Format([DueDate],"mm/dd/yyyy") AS DueDateFormat
FROM (MAXDate LEFT JOIN TATDays ON (MAXDate.MAXDateYear = TATDays.Year) AND (MAXDate.MAXDateMonth = TATDays.Month)) LEFT JOIN TATRush ON (MAXDate.LoanNumber = TATRush.LoanNumber) AND (MAXDate.ReportingPeriod = TATRush.ReportingPeriod)
GROUP BY MAXDate.ReportingPeriod, MAXDate.LoanNumber, MAXDate.[Property Count (<>SUM)], MAXDate.MaxDate, IIf([TATRush] Is Null,[TATDays],[TATRush]), Maximum([MaxDate].[DateAssigned],[RushReqDate],[ResolvedIssueDate])+[TotalTATTime], Format([DueDate],"mm/dd/yyyy"), MAXDate.DateAssigned, MAXDate.RushReqDate, MAXDate.ResolvedIssueDate, TATRush.TATRush, TATDays.TATDays;

What I would like to try though if it's possible to exclude weekends now?

Thank you guys

 

[SkipVought]

You could use the Mod function like

[yourdate] Mod 7 > 1

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

Public Function dateAddNoWeekends(dtmDate As Variant, intDaysToAdd As Integer) As Variant
  Dim direction As Integer
  Dim intCount As Integer
If IsNumeric(intDaysToAdd) And IsDate(dtmDate) Then
    dateAddNoWeekends = dtmDate
    If intDaysToAdd < 0 Then
     direction = -1
    ElseIf intDaysToAdd > 0 Then
     direction = 1
    Else
     Exit Function
    End If
    Do
        dateAddNoWeekends = dateAddNoWeekends + 1 * (direction)
        If Not (Weekday(dateAddNoWeekends) = vbSaturday Or Weekday(dateAddNoWeekends) = vbSunday) Then
        intCount = intCount + 1
        End If
    Loop Until intCount = Abs(intDaysToAdd)
End If
End Function

Select ...
dateAddNoWeekends(Maximum([MaxDate].[DateAssigned],[RushReqDate],[ResolvedIssueDate]),[TotalTATTime]) As DueDate....

 

[iuianj07]

thank you MajP, it is working correctly now.

Do you mind if you could explain to me line by line what this function does? I would really appreciated it if you did.

Thank you very much.

 

[MajP]

In vb the weekday function takes a date and returns the number for the day of the week
1 - sunday
2 - monday
..
7 - Saturday

You pass in your date and the number of days to add

Public Function dateAddNoWeekends(dtmDate As Variant, intDaysToAdd As Integer) As Variant
  'Pass in your date: dtmDate
  'Pass in the number of days to add: intDaysToAdd

  'Direction: Determine if you are adding or subtracting days.  If days to add is negative (-1), positive (1), or
  '0 then do nothing. 
  Dim direction As Integer
  'You need to loop the number of days. But only increment the counter if it is not a weekend. Not a weekend is 
  ' a weekday value of 1 (vbSunday) or 7 (vbSaturday)
  Dim intCount As Integer
  'just make sure they pass in legitimate values or do nothing
 If IsNumeric(intDaysToAdd) And IsDate(dtmDate) Then
    'set the return value equal the date passed in.
    dateAddNoWeekends = dtmDate
    'Determine if you add a day, delete a day, or exit the function
    If intDaysToAdd < 0 Then
     direction = -1
    ElseIf intDaysToAdd > 0 Then
     direction = 1
    Else
     Exit Function
    End If
    'Start looping until you go intDaysToAdd, but only increment if not a weekend
    Do
        dateAddNoWeekends = dateAddNoWeekends + 1 * (direction)
        If Not (Weekday(dateAddNoWeekends) = vbSaturday Or Weekday(dateAddNoWeekends) = vbSunday) Then
        intCount = intCount + 1
        End If
    Loop Until intCount = Abs(intDaysToAdd) 
    'need the absolute value because if they pass in -5 you want to
    'increment 5 times
End If
End Function

 

[iuianj07]

Thank you very much MajP. I appreciate the extra time to explain this to me. This helped me understand the whole function/logic, then maybe next time I could write it myself if I face a similar problem.

Again, Thank you very much

 

[MajP]

also don't be afraid to search on the web for functions. There is a ton of vb date functions on the web. I can write functions like this pretty quick, but I always try to see if something already exists or if there is an easier idea than what I was thinking.

 

[iuianj07]

thanks, yeah everytime I post something here, I also search for functions or ideas on the web, normally I would have 10 extra tabs on other websites that has functions, sometimes I just cant tweak it to match what I really need, then that's the time I post it out here. I'm getting kinda frustrated by myself now considering I'm a comsci grad

 

[MajP]

You may be interested in this. It is usually the next question.

Public Function isHoliday(ByVal dtmDate As Date) As Boolean
   Dim intYear As Integer
   Dim intMonth As Integer
   Dim intDay As Integer
   Dim intWeekDay As Integer
   intYear = Year(dtmDate)
   intMonth = Month(dtmDate)
   intDay = Day(dtmDate)
   intWeekDay = Weekday(dtmDate)
   
   'If you do not care if a holiday falls on a weekend
   'If intWeekDay > 1 And intWeekDay < 7 Then exit Function
    'New Years Day
     If DatePart("y", dtmDate) = 1 Then
       isHoliday = True
       Exit Function
     End If
    'ML King 3rd Monday of Jan
       If DayOfNthWeek(intYear, 1, 3, vbMonday) = dtmDate Then
          isHoliday = True
          Exit Function
       End If
    'Presidents Day  3rd Monday of Feb
    If DayOfNthWeek(intYear, 2, 3, vbMonday) = dtmDate Then
          isHoliday = True
          Exit Function
    End If
    'Memorial Day    Last Monday of May
      If LastMondayInMonth(intYear, 5) = dtmDate Then
         isHoliday = True
         Exit Function
      End If
    'Independance Day
       If intMonth = 7 And intDay = 4 Then
          isHoliday = True
          Exit Function
       End If
    'Labor Day   1st Monday of Sep
        If DayOfNthWeek(intYear, 9, 1, vbMonday) = dtmDate Then
          isHoliday = True
          Exit Function
       End If
    'Columbus Day    2nd Monday of Oct
        If DayOfNthWeek(intYear, 10, 2, vbMonday) = dtmDate Then
          isHoliday = True
          Exit Function
       End If
    'Thanksgiving Day  4th Thursday of Nov
       If DayOfNthWeek(intYear, 11, 4, vbThursday) = dtmDate Then
          isHoliday = True
          Exit Function
       End If
    'CHRISTMAS
        If intMonth = 12 And intDay = 25 Then isHoliday = True
End Function
Public Function DayOfNthWeek(intYear As Integer, intMonth As Integer, N As Integer, vbDayOfWeek As Integer) As Date
  'Thanksgiving is the 4th thursday in November(11)
  'dayOfNthWeek(theYear,11,4,vbThursday)
   DayOfNthWeek = DateSerial(intYear, intMonth, (8 - Weekday(DateSerial(intYear, intMonth, 1), _
 (vbDayOfWeek + 1) Mod 8)) + ((N - 1) * 7))
End Function
Function LastMondayInMonth(intYear As Integer, intMonth As Long) As Date
    'Used for memorial day
    Dim LastDay As Date
    'define last day of the month of interest:
    LastDay = DateSerial(intYear, intMonth + 1, 0)
    'use to get last monday:
    LastMondayInMonth = LastDay - Weekday(LastDay, vbMonday) + 1
End Function

Public Sub Test()
  'This appears to catch all holidays
  Dim dtmDate As Date
  Dim strHol As String
  dtmDate = #1/1/2010#
  
  Do
    strHol = strHol & "Date: " & dtmDate & " Day: " & Format(Weekday(dtmDate), "ddd") & " Holiday: " & isHoliday(dtmDate) & vbCrLf
    dtmDate = dtmDate + 1
  Loop Until dtmDate = #1/1/2011#
  Debug.Print strHol
End Sub

 

[MajP]

Forgot one
' Veteranss Day
' Although originally scheduled for celebration on November 11,
' starting in 1971 Veterans Day was moved to the fourth Monday of October.
' In 1978 it was moved back to its original celebration on November 11.
If intMonth = 11 And intDay = 11 Then
isHoliday = True
Exit Function
End If

 

[iuianj07]

Thank you very much MajP... If it's just possible to add another star on your name for this post I would do it or if there are other ways to thank you on this forum, let me know

Thanks again

 

[MajP]

BTW, I modified the above code to make it a little cleaner, but again I found the majority on the web.

 

[MajP]

However, with holidays you probably are better building a table for the year and reading the table. For example here are some exceptions for this year as it relates to US gov.

July 4, 2010 (the legal public holiday for Independence Day), falls on a Sunday. For most Federal employees, Monday, July 5, will be treated as a holiday for pay and leave purposes.

December 25, 2010 (the legal public holiday for Christmas Day), falls on a Saturday. For most Federal employees, Friday, December 24, will be treated as a holiday for pay and leave purposes.

Banks and buisnesses may have different rules for the exceptions, and some are determined during that year based on some bosses preference.

 

[iuianj07]

That's true, maybe I need to put it in a table and have a lookup from there. Especially when Holidays fall on a weekend and we get either Friday or Monday off.

When using a table for holidays, do we also use the DLookup function?

Thanks