[AccessD] Days Past Due - Grace Period

Virgina,
 
Oops!  Use - 5 instead of +5 (or -4 or -6).  Also, avoid using the Now()
function unless you really need the time component, and I don't think you do
here.  However, I've seen the situation where Date() didn't work on some
older computers but Now() always did.  To fix that I you can paste this
function into a standard module, and just use CurrentDate where ever you
would have used Date() or Now().
 
            Public Function CurrentDate() As Date
            On error GoTo EH

    CurrentDate = DatePart("m", Now()) & "/" & DatePart("d", Now()) & "/" &
DatePart("yyyy", Now())
    Exit Function
 
EH:
    Application.Echo True
    'Call GlobalErrors("", Err.Number, Err.Description, CurrentObjectName,
"CurrentDate")
    
End Function 

Now for Holidays - You need to create a table for Holidays that your client
will add to once a year in December.  The reason for this is that many
companies don't use all the Federal holidays, and many have their own
Holidays which they change every year (i.e., July 3 or July 5 or neither?).
No way you as a developer will know what they're going to do in the coming
years.  Once this table is populated, your Holiday checking code can use it
to skip the correct Holidays for that company.  If they forget to add in the
Holidays, it's their issue which they can quickly fix.
 
I believe that the ADH has code (on the discs) which will skip Weekends.
You'll need to modify that code and/or add to it to exclude Holidays listed
in the Holiday table.
 
Good Luck!
Dan
 
 
 -----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hollis,Virginia
Sent: Thursday, September 04, 2003 5:33 AM
To: 'Access Developers discussion and problem solving'
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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030904/f724e817/attachment-0001.html>