Mike and Doris Manning
mikedorism at ntelos.net
Fri Sep 5 16:24:31 CDT 2003
Here is the pseudo-code for the calculation I use...
Step 1: Count the number of days between the two dates
Step 2: Count the number of weeks between the two dates and multiply the
result by 2 (make sure to set FirstDayOfWeek to Monday)
Step 3: Count the number of holidays between the two periods
Step 4: Calculate the result (DaysResult = Step 1 - Step 2 - Step 3 + 1)
Step 5: Figure out if the result meets your "past due" criteria
Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hollis,Virginia
Sent: Friday, September 05, 2003 4:49 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.
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