Adding 5 working days to a start date

[Santiagoa]

If I set up a task table with an Date_assigned and a number of days to complete the task I calculate the end_date field by using the code below I found in this forum

How ever when I enter the Date_assigned and update the record, nothing happens until I manually enter a value in the DaysToComplete field. I want to keep DaysToComplete Constant (5 Days) so I tried to set the attribute in the table with 5 as the default but this does not work. Is there any way I can get the form to calculate the end date without having to enter the DaysToComplete value manually in the form? Thank you very much for your help

Public Function CountDays(startDate As Date, NoOfDays As Integer) As Integer
' Function to count no of working days
Dim tmpNo As Integer
Dim tmpDate As Date
Dim i As Integer
	tmpNo = NoOfDays
	tmpDate = startDate
	
	i = 0
	Do Until i = NoOfDays
		If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
			tmpNo = tmpNo + 1
		Else
			i = i + 1
		End If
		tmpDate = tmpDate + 1
	Loop
 
	CountDays = tmpNo
 
End Function

Private Sub StartDate_AfterUpdate()
	If Not IsNull(Me.NoOfDays) Then
		Me.EndDate = Me.StartDate + CountDays(Me.StartDate, Me.NoOfDays)
	End If
End Sub

Private Sub NoOfDays_AfterUpdate()
	If Not IsNull(Me.StartDate) Then
		Me.EndDate = Me.StartDate + CountDays(Me.StartDate, Me.NoOfDays)
	End If
End Sub

[NeoPa]

So, you just want to ignore the Me!NoOfDays field completely and use the value 5 in its stead in all situations?

[NeoPa]

If that is the case then you need to use this procedure :

Private Sub StartDate_AfterUpdate()
    If IsNull(Me!StartDate) Then Exit Sub
    Me!EndDate = Me!StartDate + CountDays(Me!StartDate, 5)
End Sub

You can lose the whole NoOfDays control as it's no longer required.

[Santiagoa]

If that is the case then you need to use this procedure :

Private Sub StartDate_AfterUpdate()
    If IsNull(Me!StartDate) Then Exit Sub
    Me!EndDate = Me!StartDate + CountDays(Me!StartDate, 5)
End Sub

You can lose the whole NoOfDays control as it's no longer required.

Thank you very much. This is exactly what I want to do. Unfurtanetly I tried it and I didn't work.

Here is the code I am using.

Public Function CountDays(Date_Assigned As Date, DaysToComplete As Integer) As Integer
' Function to count no of working days
Dim tmpNo As Integer
Dim tmpDate As Date
Dim i As Integer
    tmpNo = DaysToComplete
    tmpDate = Date_Assigned
    
    i = 0
    Do Until i = DaysToComplete
        If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
            tmpNo = tmpNo + 1
        Else
            i = i + 1
        End If
        tmpDate = tmpDate + 1
    Loop
 
    CountDays = tmpNo
 
End Function

And this is the code for the Date_Assigned Control

Private Sub Date_Assigned_AfterUpdate()
    If IsNull(Me.Date_Assigned) Then Exit Sub
        Me.Turnaround = Me.Date_Assigned + CountDays(Me.Date_Assigned, 5)
    End If
End Sub

[Santiagoa]

Thank you very much. This is exactly what I want to do. Unfurtanetly I tried it and I didn't work.

Here is the code I am using.

Public Function CountDays(Date_Assigned As Date, DaysToComplete As Integer) As Integer
' Function to count no of working days
Dim tmpNo As Integer
Dim tmpDate As Date
Dim i As Integer
    tmpNo = DaysToComplete
    tmpDate = Date_Assigned
    
    i = 0
    Do Until i = DaysToComplete
        If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
            tmpNo = tmpNo + 1
        Else
            i = i + 1
        End If
        tmpDate = tmpDate + 1
    Loop
 
    CountDays = tmpNo
 
End Function

And this is the code for the Date_Assigned Control

Private Sub Date_Assigned_AfterUpdate()
    If IsNull(Me.Date_Assigned) Then Exit Sub
        Me.Turnaround = Me.Date_Assigned + CountDays(Me.Date_Assigned, 5)
    End If
End Sub

I removed NoOfDays from the task table and renamed EndDate to Turnaround

[Santiagoa]

Do I need to modify the function by removing DaysToComplete?

Sorry I am trying to tweak the code but I don't really understand what it's doing.

here is what I have again

Public Function CountDays(Date_Assigned As Date, DaysToComplete As Integer) As Integer
' Function to count no of working days
Dim tmpNo As Integer
Dim tmpDate As Date
Dim i As Integer
    tmpNo = DaysToComplete
    tmpDate = Date_Assigned
    
    i = 0
    Do Until i = DaysToComplete
        If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
            tmpNo = tmpNo + 1
        Else
            i = i + 1
        End If
        tmpDate = tmpDate + 1
    Loop
 
    CountDays = tmpNo
 
End Function

For the Control

Private Sub Date_Assigned_AfterUpdate()
    If IsNull(Me!Date_Assigned) Then Exit Sub
        Me!Turnaround = Me!Date_Assigned + CountDays(Me!Date_Assigned, 5)
End Sub

Sorry Don't mean to bug you with all these questions but I am bit stuck.

[NeoPa]

Do I need to modify the function by removing DaysToComplete?

No!
Your code should be :

Public Function CountDays(Date_Assigned As Date, _
                          DaysToComplete As Integer) As Integer
'Function to return no of days forward to match no of working days passed
Dim tmpDate As Date
Dim i As Integer
    CountDays = DaysToComplete
    tmpDate = Date_Assigned
    
    For i = 1 To DaysToComplete
        If Weekday(tmpDate, vbSaturday) < 3 Then
            CountDays = CountDays + 1
            i = i - 1
        End If
        tmpDate = tmpDate + 1
    Next i
End Function

For the Control

Private Sub Date_Assigned_AfterUpdate()
    If IsNull(Me!Date_Assigned) Then Exit Sub
    Me!Turnaround = Me!Date_Assigned + _
                    CountDays(Me!Date_Assigned, 5)
End Sub

[Santiagoa]

I tried the code above exactly as posted but the turnaround box does not update when a date is entered in the Date_Assigned box and the form is refreshed. Thanks for your help

[NeoPa]

Will have to look at this tomorrow. Sorry - I'm out tonight.
Expect rational response then ;)

[Santiagoa]

Will have to look at this tomorrow. Sorry - I'm out tonight.
Expect rational response then ;)

Thank you very much for your help. Have a good night!

[NeoPa]

I tried the code above exactly as posted but the turnaround box does not update when a date is entered in the Date_Assigned box and the form is refreshed. Thanks for your help

What do you mean by "...and the form is refreshed."?
This code should update the Turnaround control immediately the Date_Assigned control has a date entered.

[Santiagoa]

What do you mean by "...and the form is refreshed."?
This code should update the Turnaround control immediately the Date_Assigned control has a date entered.

The date assigned control uses a calendar to assigned the date. I added a refresh data button see if this might help.

[Santiagoa]

What do you mean by "...and the form is refreshed."?
This code should update the Turnaround control immediately the Date_Assigned control has a date entered.

The date_assigned control uses a calendar to assign the date. When I remove the calendar control the code works fine. How can I use the calendar and still get the code to populate the Turnaround control?

[NeoPa]

The date assigned control uses a calendar to assigned the date. I added a refresh data button see if this might help.

You should understand that introducing something new into the mixture, without informing anyone, is likely to cause confusion. It's hard enough to think about what's happening your end without unnotified changes going on.

I need you to explain now, what happened without the refresh data button. I expect that it didn't work, but I need that expressed clearly rather than my trying to read between the lines.

[NeoPa]

The date_assigned control uses a calendar to assign the date. When I remove the calendar control the code works fine. How can I use the calendar and still get the code to populate the Turnaround control?

Sorry, I didn't catch this post earlier (It's still not a good idea to play around with the situation until the earlier problem is resolved). When you say your first sentence above, what do you mean exactly? Does it still use the Date_Assigned control or is the Calendar control used with a different name?
Try to explain exactly what you did to implement the Calendar control into the system.