Drew Wutka
DWUTKA at marlow.com
Mon Sep 8 13:08:07 CDT 2003
I am sending you a new copy of the db you sent me.
The module has been changed to this:
Function PastDue(dtEntered As Date, strStatus) As Long
Dim i As Long
Dim tmpDate As Date
If strStatus = "Pending" Then
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
Else
PastDue = 0
End If
End Function
(I left the strStatus argument as a variant, in case it is ever passed a
null)
I also changed the query the form is bound too (to accept the changes to the
function), and I changed your form. What I changed in the form, is instead
of doing a double compare to determine if the label should be displayed or
not, I just have it checking if Me.PastDue>0. (Since the new function takes
the Status Field into account)
Drew
-----Original Message-----
From: Hollis,Virginia [mailto:HollisVJ at pgdp.usec.com]
Sent: Monday, September 08, 2003 11:48 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Days Past Due - Grace Period
Not exactely (make sense) but, the Status field determines if the past due
message should show. The past due message shows if the project is Pending
(status = 1) & over 5 days past due.
-----Original Message-----
From: Drew Wutka [mailto:DWUTKA at marlow.com]
Sent: Monday, September 08, 2003 11:24 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Days Past Due - Grace Period
Don't mind at all! ;)
Quick question (I'm a perfectionist...), is there a field that determines if
the record has been taken care of? My method does a 'count back', which
will work fine even for records that are years PastDue (because your
computer can count a few thousand in milliseconds...), but it would be
smarter to wrap the loop in an If Then Else statement, (and include a second
argument in the function), so if that second argument meets the criteria,
the function just returns 0, otherwise it calculates with the loop. Make
sense?
Drew
-----Original Message-----
From: Hollis,Virginia [mailto:HollisVJ at pgdp.usec.com]
Sent: Monday, September 08, 2003 11:04 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Days Past Due - Grace Period
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?
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
_______________________________________________
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