Rocky Smolin - Beach Access Software
bchacc at san.rr.com
Thu Sep 4 09:36:16 CDT 2003
That's how I do it in my manufacturing system where I have to calculate
start date from due date minus lead time. I have a user maintainable
holiday calendar as well as allowing them to select which days are work
days. So the function which calculates Order Date from Due Date skips
around the non-work days and holidays. Real PITA.
Rocky
----- Original Message -----
From: "Don Elliker" <delliker at hotmail.com>
To: <accessd at databaseadvisors.com>
Sent: Thursday, September 04, 2003 7:13 AM
Subject: Re: [AccessD] Days Past Due - Grace Period
> Yeah Rocky, as long as Holidays aren't an issue, and eventually they are.
> Big fun in function land- can you say Easter?? Probably the best bet is to
> have a table the users can maintain with the company holidays in it. A
> simple Dlook() on such a small table should be cheap enough.
> _D
>
>
>
> "Things are only free to the extent that you don't pay for them".-Don
> Elliker
>
>
>
>
>
> >From: "Rocky Smolin - Beach Access Software" <bchacc at san.rr.com>
> >Reply-To: Access Developers discussion and problem
> >solving<accessd at databaseadvisors.com>
> >To: "Access Developers discussion and problem
> >solving"<accessd at databaseadvisors.com>
> >Subject: Re: [AccessD] Days Past Due - Grace Period
> >Date: Thu, 4 Sep 2003 06:36:29 -0700
> >
> >MessageVirginia:
> >
> >The only way I've found to do this is to loop through the days in code
from
> >the first day to the last day, getting the day of the week and
incrementing
> >a counter if it's not a Saturday or a Sunday using the Weekday function..
> >
> >Possibly you could count the days between two dates by building it into a
> >summation query using 'Weekday(thedate)<>vbSaturday and
> >Weekday(thedate)<>vbSunday' as a criterion.
> >
> >Actually, as I think about it, you should make the whole thing a public
> >function, passing the two dates and returning the number of weekdays.
Then
> >you could call it from anywhere including in a query.
> >
> >HTH
> >
> >
> >Rocky Smolin
> >Beach Access Software
> >
> > ----- Original Message -----
> > From: Hollis,Virginia
> > To: 'Access Developers discussion and problem solving'
> > Sent: Thursday, September 04, 2003 4:33 AM
> > Subject: RE: [AccessD] Days Past Due - Grace Period
> >
> >
> > 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
>
> _________________________________________________________________
> Fast, faster, fastest: Upgrade to Cable or DSL today!
> https://broadband.msn.com
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>