Don Elliker
delliker at hotmail.com
Thu Sep 4 09:13:12 CDT 2003
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