[AccessD] Days Past Due - Grace Period

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030904/9cf131cf/attachment-0001.html>