[AccessD] Days Past Due - Grace Period

Stephen Hait shait at mindspring.com
Thu Sep 4 16:24:42 CDT 2003



> 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
> 
> > Ok, I changed the query to the below code, is this the correct way
> > to do this?
> > 
> > PastDue: DateDiff("d",[DateEntered],Now())-5
> > 
> > Oh! Get this, I just got an email from mgmt, they want the days
> > past due to count "Buisness Days only". So it does not count
> > weekends & holidays!! I can't figure out how to make it count to 5
> > let alone skip weekends. :-(
> > 
> > Va.
> > 
> > -----Original Message-----
> > From: Hollis,Virginia [mailto:HollisVJ at pgdp.usec.com]
> > Sent: Thursday, September 04, 2003 6:15 AM
> > To: 'Access Developers discussion and problem solving'
> > Subject: RE: [AccessD] Days Past Due - Grace Period
> > 
> > 
> > No, that did not work. For a project that was entered 8/27/03, it
> > shows 12.522 days. The old code shows it 8 days past due, but I
> > need it to show 3 days past due.
> > 
> > I hope someone can help me with this because I had a user complain
> > to upper mgmt about this, and it is causing me problems.
> > 
> > Va.
> > 
> > -----Original Message-----
> > From: Dan Waters [mailto:dwaters at usinternet.com]
> > Sent: Wednesday, September 03, 2003 3:24 PM
> > To: 'Access Developers discussion and problem solving'
> > Subject: RE: [AccessD] Days Past Due - Grace Period
> > 
> > 
> > Virginia,  I added some code into your example.  Would this work?
> > 
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> > Hollis,Virginia Sent: Wednesday, September 03, 2003 1:25 PM To:
> > 'accessD at databaseadvisors.com' Subject: [AccessD] Days Past Due -
> > Grace Period
> > 
> > 
> > A project review must be completed within 5 days. If a project
> > review is over 5 days past due and the status is Pending, a label
> > shows "Past Due & the number of days it is past due". The Past Due
> > date is based on the date the record was entered into the system.
> > 
> > Problem: I need the past due label to show not the days past due
> > from the DateEntered, but count the days Over 5. For example, the
> > project was entered 8/28/2003, once the project is >= 5 days past
> > 8/28/2003 or on 9/4/2003 the label shows this project is past due
> > 6 days. What I really need is the label to show it is past due 1
> > day, because they do have a 5 day grace period. I use this on
> > forms & the reports.
> > 
> > Virginia
> > 
> > ***************
> > 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




More information about the AccessD mailing list