[AccessD] Days Past Due - Grace Period

Stephen Hait shait at mindspring.com
Mon Sep 8 13:50:10 CDT 2003


> Maybe that is the problem - subtracting the 5 day grace period from
> the total number of work days it is past due. Where would that go?
> Drew sent me something that works. (see below). But I would like to
> know what I was doing wrong with your example?

The main difference between Drew's function (which you include 
below) and the approach I provided looks to be as follows: 
Drew's approach uses a function to calculate the number of days 
past due. That is, the logic of what constitues past due is hard 
coded in the function. If there are more than 5 weekdays between 
the two dates then the value of the returned variable, PastDue, 
would be a positive number. If there are fewer than 5 weekdays 
between the two dates then the the value of the variable would be 
zero or a negative number. So the logic of determining whether 
the project is past due is contained in the function and the result 
of the function (the number stored in PastDue) would then 
determine what you would do as a result. 
Advantage - you have the number of days past due immediately 
when the function returns the variable PastDue. 
Disadvantage - could not easily reuse the function for other 
situations requiring calculating the number of weekdays between 
two dates.

My approach uses a function (from Dev Ashish's website and 
included immediately below for reference) that simply calculates 
the number of week days between any two dates. The logic of 
determining what constitutes past due is handled by the code 
that calls the function (rather than the function itself) but the 
result of the function would determine what you would do as a 
result similar to Drew's approach. 
Advantage - you could reuse the function for other situations 
requiring calculating the number of weekdays between two dates.
Disadvantage - you do not immediately have the number of days 
past due when the function returns the variable PastDue.

Using either approach, though, would still require your code 
referencing the function to analyze the value returned from the 
function to determining whether to treat this situation as one 
being past due or not past due.

****** from Stephen ********
Function Work_Days(BegDate As Variant, EndDate As Variant) 
As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

    BegDate = DateValue(BegDate)

    EndDate = DateValue(EndDate)
        WholeWeeks = DateDiff("w", BegDate, EndDate)
        DateCnt = DateAdd("ww", WholeWeeks, BegDate)
        EndDays = 0
        Do While DateCnt < EndDate
            If Format(DateCnt, "ddd") <> "Sun" And _
                          Format(DateCnt, "ddd") <> "Sat" Then
                    EndDays = EndDays + 1
            End If
            DateCnt = DateAdd("d", 1, DateCnt)
        Loop
        Work_Days = WholeWeeks * 5 + EndDays
End Function
***** end from Stephen ******

HTH,
Stephen

> I don't think Drew would mind me posting his work - in case someone
> else is having the same problem.
> 
> ********* from Drew***********
> Function PastDue(dtEntered As Date) As Long
> Dim i As Long
> Dim tmpDate As Date
> tmpDate = Date
> i = 0
> Do Until tmpDate <= dtEntered
>     tmpDate = tmpDate - 1
>     If WeekDay(tmpDate) <> 1 And WeekDay(tmpDate) <> 7 Then i = i +
>     1
> Loop
> PastDue = i - 5
> End Function
> 
> -----Original Message-----
> From: Stephen Hait [mailto:shait at mindspring.com]
> Sent: Monday, September 08, 2003 10:37 AM
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] Days Past Due - Grace Period
> 
> 
> 
> > Ok, the way they want it figured...
> > 
> > They have a 5 day grace period before it is past due. So
> > DateEntered is 8/27/03, they have until Wednesday 9/3 to review
> > the project. Then they want it to start counting after the 5 day
> > grace period (work days, not weekends). So the project will not be
> > past due until Thursday, the 4th, then it is 1 day past due (1 day
> > past the grace period).
> 
> I'm not sure what the problem you're having is. The code I posted
> (see below) returns the number of week days between two dates. In
> your case the two dates are dateEntered from your table and todays
> date. When these dates are 8/27/03 and 9/4/03 the number of weekdays
> returned is 6. In the example I provided, 6 weekdays is greater than
> your 5 day grace period so the test would result in the overdue
> message being displayed. If you want to also include in your overdue
> message the number of days overdue, you would simply subtract 5
> (your grace period) from the number of weekdays (6) which results in
> 1 day overdue which is what you have just described.
> 
> If this is still confusing, please describe what you are confused
> by.
> 
> Regards,
> Stephen
> 
> > -----Original Message-----
> > From: Stephen Hait [mailto:shait at mindspring.com]
> > Sent: Friday, September 05, 2003 9:17 PM
> > To: Access Developers discussion and problem solving
> > Subject: RE: [AccessD] Days Past Due - Grace Period
> > 
> > 
> > 
> > 
> > > Thanks for the help.
> > > 
> > > It is still counting the weekends. For a project that has a
> > > DateEntered of 8/27/2003 - it shows 4 days past due. It should
> > > show 1 day past due.
> > 
> > This solution does not count weekends for me. Since it is Sep. 5
> > when you wrote this, the correct result if DateEntered was
> > 8/27/2003 should probably be 7 days late (8/27/2003 is a Wednesday
> > and today [9/5/2003] is a Friday). The number of week days late
> > should be calculated based on the week days 8/28, 8/29, 9/1, 9/2,
> > 9/3, 9/4, 9/5 which is 7 week days after Wed., 8/27/2003. If you
> > are trying to accomplish something other than this then this is
> > not the solution you should be using.
> > 
> > Let me know if I am missing something here.
> > 
> > Regards,
> > Stephen
> > 
> > > Va.
> > > 
> > > -----Original Message-----
> > > From: Stephen Hait [mailto:shait at mindspring.com]
> > > Sent: Friday, September 05, 2003 3:16 PM
> > > To: accessd at databaseadvisors.com
> > > Subject: RE: [AccessD] Days Past Due - Grace Period
> > > 
> > > 
> > > 
> > > 
> > > > I am not sure what you mean by 'pass' the dates. How do I tell
> > > > the function to look at the DateEntered as the BegDate?
> > > > 
> > > > The table has a field DateEntered. The form has a text box
> > > > with the ControlSource(="Review is " & [PastDue] & " days past
> > > > due") PastDue comes from the query (PastDue:
> > > > DateDiff("d",[DateEntered],Now())-5) Then OnCurrent of the
> > > > form,(If DateEntered <= DateAdd("d", -5, Date) And StatusID =
> > > > 1 'Pending) the text box shows the number of days it is past
> > > > due.
> > > 
> > > In this case, here is a generalized approach. You want your text
> > > box to contain a message indicating if the review is past due
> > > and, if so, by how many days. Since it appears you want to
> > > dynamically set the value of the text box message, you might do
> > > the following to set your control text in the OnCurrent event of
> > > your form:
> > > 
> > > If StatusID = 1 and WorkDays([DateEntered],Date()) > 5 Then
> > >  display past due notice here
> > > Else
> > >  display not past due notice here
> > > End If
> > > 
> > > HTH,
> > > Stephen
> > > 
> > > > Va.
> > > > 
> > > > -----Original Message-----
> > > > From: Stephen Hait [mailto:shait at mindspring.com]
> > > > Sent: Thursday, September 04, 2003 4:25 PM
> > > > To: Access Developers discussion and problem solving
> > > > Subject: RE: [AccessD] Days Past Due - Grace Period
> > > > 
> > > > 
> > > > 
> > > > 
> > > > > What do I have to do to make this work from the form &/or
> > > > > report?
> > > > > 
> > > > > Va.
> > > > 
> > > > The function work_days accepts two arguments, BegDate and
> > > > EndDate, and returns the number of weekdays between them. In
> > > > your case, you're interested in the number of weekdays between
> > > > a due date and today and if it's higher than 5, you want to
> > > > take some action. 
> > > > 
> > > > I have no idea what your form or report looks like but if you
> > > > have the due date of a particular item where the status is
> > > > Pending, you would pass that as the BegDate and pass Date() as
> > > > the end date to the function. The value returned would be the
> > > > number of workdays between these two dates and you could
> > > > proceed accordingly.
> > > > 
> > > > HTH,
> > > > Stephen
> > > > 
> > > > 
> > > > > -----Original Message-----
> > > > > From: Stephen Hait [mailto:shait at mindspring.com]
> > > > > Sent: Thursday, September 04, 2003 9:12 AM
> > > > > To: accessd at databaseadvisors.com
> > > > > Subject: RE: [AccessD] Days Past Due - Grace Period
> > > > > 
> > > > > 
> > > > > Here's a function to calculate number of weekdays between 2
> > > > > dates: http://www.mvps.org/access/datetime/date0006.htm
> > > > > 
> > > > > HTH,
> > > >  Stephen
> > > > > 
> > > > 
> > > > > > ***************
> > > > > > Used in the query to determine the number of days past
> > > > > > due: PastDue: DateDiff("d",[DateEntered],Now())
> > > > > > 
> > > > > > OnCurrent of the form:
> > > > > > If DateEntered <= DateAdd("d", -5, Date) And StatusID = 1
> > > > > > Then 'Status is pending and review is 5 days past due.
> > > > > >        Me!DateEntered.ForeColor = lngRed 'Date entered is
> > > > > >        red Me!LateDate.Visible = True ' Show the sad face
> > > > > >        Me.DaysPastDue.Visible = True 'Show past due label
> > > > > >        '-- Show number of days over 5 that the project
> > > > > >        review is late Me.DaysPastDue.Caption = (Date() -
> > > > > >        DateEntered) + 5 
> > > > > >     Else
> > > > > >         Me!DateEntered.ForeColor = lngBlack 'Review is not
> > > > > >         past due, Date
> > > > > > Entered is black
> > > > > >         Me!LateDate.Visible = False 'No sad face
> > > > > >         Me.DaysPastDue.Visible = False 'No past due label.
> > > > > >     End If
> > > > > > 
> > > > > > 
> > > > > 
> > > > > 
> > > > > _______________________________________________
> > > > > AccessD mailing list
> > > > > AccessD at databaseadvisors.com
> > > > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > > > Website: http://www.databaseadvisors.com
> > > > > _______________________________________________
> > > > > AccessD mailing list
> > > > > AccessD at databaseadvisors.com
> > > > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > > > Website: http://www.databaseadvisors.com
> > > > 
> > > > 
> > > > _______________________________________________
> > > > AccessD mailing list
> > > > AccessD at databaseadvisors.com
> > > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > > Website: http://www.databaseadvisors.com
> > > > _______________________________________________
> > > > AccessD mailing list
> > > > AccessD at databaseadvisors.com
> > > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > > Website: http://www.databaseadvisors.com
> > > 
> > > 
> > > _______________________________________________
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > > _______________________________________________
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > 
> > 
> > _______________________________________________
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> > _______________________________________________
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> 
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com




More information about the AccessD mailing list