[AccessD] Days Past Due - Grace Period

Stephen Hait shait at mindspring.com
Mon Sep 8 10:37:20 CDT 2003


> 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




More information about the AccessD mailing list