Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    48

    Unanswered: Date calculations in Access

    How do I adapt the following so that the "new date" ignores Saturday and/ or Sunday? For example: the user selects April 7 from a calendar control. He/she wants the new date to be 5 business days from the date selected. The new date should be April 14 because 5 business days from April 7 should not consider Saturday and Sunday as business days.

    Dim FirstDate As Date
    Dim IntervalType As String
    Dim Number As Integer
    Dim Msg
    IntervalType = "d" '"d" specifies days as interval
    FirstDate = Calendar1
    Number = InputBox("Enter number of days to add")
    Msg = "New date: " & DateAdd(IntervalType, Number, FirstDate)


    I'm also trying to figure out how to ignore holidays ( I have a holiday table).

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Perhaps this article by Microsoft will help: http://support.microsoft.com/kb/210562

    But it doesn't take into account holidays which you may have to use your holiday table for in the function.
    Last edited by pkstormy; 04-07-08 at 16:31.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I tend to loop adding a day to the target date until the date doesn't start with S and isn't in the holiday table.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •