Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Location
    Wales
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date expression (Access 97)

    New to Access development. Have created a database and a user form. The form has an order date to which I wish to add 3 working days (monday to friday) to automatically calculate a new 'target date' in a seperate box on the form. I cannot work out how to exclude dates that include saturday or sunday. It's easy to add [Oder No Date]+3 but how are weekends excluded? Thank you in advance for any helpful responses.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date expression (Access 97)

    Look up the function DateAdd in Access Help. You will see one of the arguments is interval. There is an interval for weekdays.
    HTH

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Date expression (Access 97)

    You basically need a function to do this, as it takes a bit of code. Essentially you have to add 1 day at a time, if it new day falls on a weekend, increment the day and check gain.<pre>Public Function DateAddBusinessDays(ByVal BusDays As Integer, ByVal StartDate As Date) As Date
    'This returns the date after adding a number of business days (BusDAys) to a StartDate.
    'Weekends are skipped (No provision for Holidays).

    Dim intDays As Integer
    Dim dt As Date

    intDays = BusDays
    dt = StartDate

    Do While intDays > 0
    intDays = intDays - 1
    Do
    dt = dt + 1
    Loop While (WeekDay(dt, vbMonday) >= 6)
    Loop

    DateAddBusinessDays = dt

    End Function</pre>

    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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