Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Red face Unanswered: Help with VB Code business days

    I have the following code:

    ____________________

    Private Sub Policy_Status_AfterUpdate()
    If Me.[Policy_Status] = "Pending 1" Then
    Me.[FollowUpDate] = Date + 5

    Else
    ' remove date if reopened or set to other status
    Me.[FollowUpDate] = Null

    If Me.[Policy_Status] = "Pending 2" Then
    Me.[FollowUpDate] = Date + 5

    Else
    ' remove date if reopened or set to other status
    Me.[FollowUpDate] = Null

    End If
    End If

    End Sub
    _________________

    It does function, but have 2 questions. Is there a way that I can do an OR, in the IF statement, saying IF = Pending 1 OR IF = Pending 2???

    It does add 5 days to the follow-up date, but I want to exclude Sat and Sun. how can i do that?


  2. #2
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Try using something alone these lines:

    Code:
            Me.[FollowUpDate] = Dateadd("w", 5, Me.[FollowUpDate])
    This will add 5 weekdays to your date, which if memory serves is M-F instead of Sunday-Saturday.

    Sam, hth
    Good, fast, cheap...Pick 2.

  3. #3
    Join Date
    Nov 2003
    Posts
    300
    Thanks, but the code failed to do anything.

  4. #4
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    D'oh!
    Change that to:
    Code:
    Me.[FollowUpDate] = Dateadd("w", 5, Date)
    I had it adding 5 days to what I'm guessing was a null field...not gonna work.

    Sam, sorry...long day
    Good, fast, cheap...Pick 2.

  5. #5
    Join Date
    Nov 2003
    Posts
    300
    Well, now I am getting it to work with the 5 days, but 7/31/2010 is a Saturday, it should put in 8/2/2010..

    getting close... ??

  6. #6
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Try using a simple select after adding the original 5 days to the date.

    Code:
    Select Case Weekday(me.FollowUpDate)
    
    Case 1: ' Sunday -- Add One Day
         Me.FollowUpDate = Dateadd("w", 1, Me.FollowUpDate)
    Case 7: 'Saturday -- Add Two Days
         Me.FollowUpDate = Dateadd("w", 2, Me.FollowUpDate)
    
    End Select
    After a quick search I was wrong (Imagine that!), for some reason I was thinking the "w" or "Weekday" interval on the DateAdd went to the next business day...it doesn't, so you need to check against the day and make the necessary changes.

    Sam, sorry 'bout that
    Good, fast, cheap...Pick 2.

Posting Permissions

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