Stephen Hait
shait at mindspring.com
Fri Sep 5 21:16:43 CDT 2003
> 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