Results 1 to 9 of 9
  1. #1
    venkat_m is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    4

    Question please help in adding codes for skip weekends in the below VBA Code

    Given Data: "Requested On" Date and Priority of work (Low, medium and high). Based on which planned completion date changes.


    low priority work takes 6 days, medium priority work takes 4 days, high priority work takes 2 days.

    For Example: if work requested on 12-apr-19 provided if high priority work - then planned completion date should be 16-apr-19 because of weekends.

    Problem: Need to skip weekends sat and sun. Because if the work requested on Friday, then work cannot be delivered by sunday EOD. So, i need a help in skipping weekend dates and directly consider weekday.



    Private Sub Priority_Change()

    If Priority.Value = "High" Then
    Planned_Completion_Date.Value = DateAdd("d", 2, Requested_On.Value)

    ElseIf Priority.Value = "Medium" Then
    Planned_Completion_Date.Value = DateAdd("d", 4, Requested_On.Value)
    ElseIf Priority.Value = "Low" Then
    Planned_Completion_Date.Value = DateAdd("d", 6, Requested_On.Value)
    End If
    End Sub
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Code:
    Private Sub Priority_Change()
    Dim iDays As Integer
    Select Case Planned_Completion_Date
       Case "High"
            iDays = 2
       
       Case "Medium"
            iDays = 4
       
       Case "Low"
            iDays = 6
    End Select
    
    Select Case Format(DateAdd("d", iDays, Requested_On), "ddd")
       Case "Sat"
          iDays = iDays + 2
       Case "Sun"
          iDays = iDays + 1
    End Select
    
    Planned_Completion_Date = DateAdd("d", iDays, Requested_On)
    End Sub

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If Requested_On = 12-Apr-19, then Planned_Completion_Date = 20-Apr-19. But 20-Apr-19 is a Saturday, so add 2 more days.
    I created a form with a combo box for the priority (named cboPriority) and used the AfterUpdate event.


    Priority Requested_On Planned_Completion_Date
    High 12-Apr-19 16-Apr-19
    Medium 12-Apr-19 18-Apr-19
    Low 12-Apr-19 22-Apr-19
    Is this correct?


    Try this
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub cboPriority_AfterUpdate()
        Dim dRequestDate As Date
        Dim dCompleteDate As Date
        Dim iNumDays As Integer
        Dim tmpDate As Date
        Dim DaysToAdd As Integer
        Dim i As Integer
    
        dRequestDate = Me.Requested_On
    
        Select Case Me.cboPriority
            Case "High"
                dCompleteDate = DateAdd("d", 2, Requested_On.Value)
            Case "Medium"
                dCompleteDate = DateAdd("d", 4, Requested_On.Value)
            Case "Low"
                dCompleteDate = DateAdd("d", 6, Requested_On.Value)
        End Select
    
        iNumDays = DateDiff("d", dRequestDate, dCompleteDate)
        DaysToAdd = 0
        tmpDate = dRequestDate + 1
    
        'check if any of the dates fall on the weekend
        For i = 1 To iNumDays
            If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
                DaysToAdd = DaysToAdd + 1
            End If
            tmpDate = DateAdd("d", 1, tmpDate)
        Next
    
        dCompleteDate = dCompleteDate + DaysToAdd
    
        'check the Complete Date to see if it is a weekend
        If Weekday(dCompleteDate) = 1 Or Weekday(dCompleteDate) = 7 Then
            dCompleteDate = dCompleteDate + 2
        End If
    
        ' put date in control
        Me.Planned_Completion_Date = dCompleteDate
    End Sub

  4. #4
    venkat_m is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    4
    if i change the "requested on" date as 12-Apr-19 and change to "high" priority , the planned completion date is not changing to 16-apr-19.
    if i change the "requested on" date as 12-Apr-19 and change to "medium" priority , the planned completion date is not changing to 18-apr-19.
    if i change the "requested on" date as 12-Apr-19 and change to "low" priority , the planned completion date is not changing to 22-apr-19.
    please check.

    Click image for larger version. 

Name:	planned completion date and priority.PNG 
Views:	19 
Size:	17.5 KB 
ID:	38098

  5. #5
    venkat_m is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    4
    if i change the "requested on" date as 12-Apr-19 and change to "high" priority , the planned completion date is not changing to 16-apr-19.
    if i change the "requested on" date as 12-Apr-19 and change to "medium" priority , the planned completion date is not changing to 18-apr-19.
    if i change the "requested on" date as 12-Apr-19 and change to "low" priority , the planned completion date is not changing to 22-apr-19.
    please check.

    Click image for larger version. 

Name:	planned completion date and priority.PNG 
Views:	19 
Size:	17.5 KB 
ID:	38099


    Quote Originally Posted by ssanfu View Post
    If Requested_On = 12-Apr-19, then Planned_Completion_Date = 20-Apr-19. But 20-Apr-19 is a Saturday, so add 2 more days.
    I created a form with a combo box for the priority (named cboPriority) and used the AfterUpdate event.


    Priority Requested_On Planned_Completion_Date
    High 12-Apr-19 16-Apr-19
    Medium 12-Apr-19 18-Apr-19
    Low 12-Apr-19 22-Apr-19
    Is this correct?


    Try this
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub cboPriority_AfterUpdate()
        Dim dRequestDate As Date
        Dim dCompleteDate As Date
        Dim iNumDays As Integer
        Dim tmpDate As Date
        Dim DaysToAdd As Integer
        Dim i As Integer
    
        dRequestDate = Me.Requested_On
    
        Select Case Me.cboPriority
            Case "High"
                dCompleteDate = DateAdd("d", 2, Requested_On.Value)
            Case "Medium"
                dCompleteDate = DateAdd("d", 4, Requested_On.Value)
            Case "Low"
                dCompleteDate = DateAdd("d", 6, Requested_On.Value)
        End Select
    
        iNumDays = DateDiff("d", dRequestDate, dCompleteDate)
        DaysToAdd = 0
        tmpDate = dRequestDate + 1
    
        'check if any of the dates fall on the weekend
        For i = 1 To iNumDays
            If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
                DaysToAdd = DaysToAdd + 1
            End If
            tmpDate = DateAdd("d", 1, tmpDate)
        Next
    
        dCompleteDate = dCompleteDate + DaysToAdd
    
        'check the Complete Date to see if it is a weekend
        If Weekday(dCompleteDate) = 1 Or Weekday(dCompleteDate) = 7 Then
            dCompleteDate = dCompleteDate + 2
        End If
    
        ' put date in control
        Me.Planned_Completion_Date = dCompleteDate
    End Sub

  6. #6
    venkat_m is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    4
    if i change the "requested on" date as 12-Apr-19 and change to "high" priority , the planned completion date is not changing to 16-apr-19.
    if i change the "requested on" date as 12-Apr-19 and change to "medium" priority , the planned completion date is not changing to 18-apr-19.
    if i change the "requested on" date as 12-Apr-19 and change to "low" priority , the planned completion date is not changing to 22-apr-19.
    please check.

    Click image for larger version. 

Name:	planned completion date and priority.PNG 
Views:	19 
Size:	17.5 KB 
ID:	38100

  7. #7
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Repeating your post three times won't get you a quicker answer.

    Ideally you shouldn't store this but simply calculate it. That way it won't need updating every time you change a date or a priority.
    You could do this with a simple function based on the code ssanfu provided.

    In fact I would make two functions one for to simply add working days you could use any where and one that used that with your priority field.

    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @venkat_m

    It has been pointed out to me that I get short sighted about date formats. Because I live in the USA, I never work with dates that are not in U.S. format. For that I apologize.
    BUT, I don't know where you reside - you didn't specify.


    It appears that you know how to program in VBA, at least a little. Did you do any troubleshooting? Single step through the code?
    Is the field "Requested_On" a date field?
    Is the field "Planned_Completion_Date" a date field?
    Did you notice that I changed the event to the AfterUpdate event of the combo box?

    You didn't answer my question about the "Planned_Completion_Date" - were they correct?


    There is a function written by Allen Browne called "SQLDate()" to convert non USA dates to USA date format. Go to that site and look for the paragraph "Dates in Strings".


    Attached is the dB I used to test the code. How are the dates?
    Attached Files Attached Files

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    venkat_m,

    While you are analyzing your requirement, I suggest you confirm you work on stat holidays. If you don't then
    you could adjust your code to deal with weekends and holidays.

    Better to get all of your requirements modeled, tested and confirmed before getting too deeply involved in a physical database.
    Good luck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 31
    Last Post: 11-08-2017, 11:50 AM
  2. Skip section of code on button click
    By NovoRapid in forum Modules
    Replies: 2
    Last Post: 02-05-2015, 02:43 PM
  3. VBA Code to exclude codes
    By kwooten in forum Programming
    Replies: 1
    Last Post: 09-10-2013, 10:39 AM
  4. Adding SQL Code to VBA
    By REAbernathy in forum Programming
    Replies: 20
    Last Post: 09-06-2012, 02:29 PM
  5. Adding 5 days to date function but exclude weekends
    By mulefeathers in forum Queries
    Replies: 1
    Last Post: 04-27-2012, 10:28 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums