Calculate end date (working day) in Access

[Thomadh]

If I set up a task table with a task start date and a number of days to complete the task (as in MS Project), how can I calculate an end date field and place it in the table, ignoring weekends (i.e. only considering Monday to Friday)
Really bugging me, can someone help please?

[MMcCarthy]

Create the field in the table, lets call it endDate

You will need to create a function in a module to handle the count of days as below

 
Public Function CountDays(startDate As Date, NoOfDays As Integer) As Date 
Dim tmpDate As Date
Dim i As Integer
 
tmpDate = StartDate
i=0
Do Until i = NoOfDays
  If WeekDay(tmpDate)<>1 Or WeekDay(tmpDate)<>7 Then
	i = i+1
	tmpDate=tmpDate+1
  End If
Loop
 
CountDays=tmpDate
 
End Function

Then run an update query as follows:

UPDATE TableName SET endDate=CountDa ys(startDate,No OfDays);

[Thomadh]

Create the field in the table, lets call it endDate

You will need to create a function in a module to handle the count of days as below

 
Public Function CountDays(startDate As Date, NoOfDays As Integer) As Date 
Dim tmpDate As Date
Dim i As Integer
 
tmpDate = StartDate
i=0
Do Until i = NoOfDays
  If WeekDay(tmpDate)<>1 Or WeekDay(tmpDate)<>7 Then
	i = i+1
	tmpDate=tmpDate+1
  End If
Loop
 
CountDays=tmpDate
 
End Function

Then run an update query as follows:

UPDATE TableName SET endDate=CountDa ys(startDate,No OfDays);

Thanks very much for this but it still seems to simply add the days to the start date and calculate taht as end date (i.e. it does not 'jump over' the weekends.)
In simple terms I have a Table with startdate,noofd ays and enddate as 3 fields and I want to take the start date add the noofdays and place the correct end WORKING DAY date in the enddate field
As I say really helpful module script,but not quite working!
Also is it pssible to embedd the code into the Table input in datasheet view (i.e. not have to run the update query as an explicit action?), so that on inputting the first 2 fields in a record the third field is calculated at input time
This is really pretty important to me so if mmccarthy or anyone else could help I would be very grateful

[MMcCarthy]

Sorry my logic was wrong. The following function will return a count of the actual no of days represented by any given number of working days. This number can then be added to the start date to get an end date (see update function below code). I've tested it and it seems to work fine, try it and see.

 
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

Run the following update query to get the end date for existing records:

UPDATE Table1 SET Table1.EndDate = [StartDate]+CountDays([StartDate],[NoOfDays]);


This fuction cannot be used in the default value on the table. Therefore, it cannot be updated through entering data directly into the table.

However, it can be used in the code in an event on a data entry form. For example in the after update event of the user entering a number in the number of days field this value could be calculate.

[Thomadh]

Sorry my logic was wrong. The following function will return a count of the actual no of days represented by any given number of working days. This number can then be added to the start date to get an end date (see update function below code). I've tested it and it seems to work fine, try it and see.

 
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

Run the following update query to get the end date for existing records:

UPDATE Table1 SET Table1.EndDate = [StartDate]+CountDays([StartDate],[NoOfDays]);


This fuction cannot be used in the default value on the table. Therefore, it cannot be updated through entering data directly into the table.

However, it can be used in the code in an event on a data entry form. For example in the after update event of the user entering a number in the number of days field this value could be calculate.

mmccarthy thanks so much for this.
I now have the update query working!
However I wonder if you can help with how I set the after update event on the input form.
I am a bit of an Access novice and my input Form is simply a Wizard construction of input fields with one or two table lookups via drop down lists.
If you could tell me how I could action the date calculate function by the after update event on form input of number of days that really would be the icing on the cake!
Thanks again, what a help to me this is, really appreciated!

[MMcCarthy]

Open the form in design view.
Open the properties window.
Click on the control for the StartDate field.
In the Event tab go to After Update
Click on the little arrow to the right and scroll to [Event Procedure] and select it.
Now click on the little button to its right with the dots on it.
The VB editor will open with the following.

Private Sub StartDate_After Update()

End Sub

You need to add the code so it looks like the following:

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

Now do exactly the same thing on the NoOfDays field. The code will be slightly different. See below:

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

[Thomadh]

Open the form in design view.
Open the properties window.
Click on the control for the StartDate field.
In the Event tab go to After Update
Click on the little arrow to the right and scroll to [Event Procedure] and select it.
Now click on the little button to its right with the dots on it.
The VB editor will open with the following.

Private Sub StartDate_After Update()

End Sub

You need to add the code so it looks like the following:

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

Now do exactly the same thing on the NoOfDays field. The code will be slightly different. See below:

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

MMCCARTHY

Thanks so much , works great!

Can I ask a couple more things (you are so good!)

1. My use of Forms has been to display and input one record on screen at a time.
Given that effectively I am looking to build a Microsoft Project type task list via Access, it would be useful to see all preceding tasks on screen at input time (i.e. as in MS Project, Excel or Access Datasheet view), but still using the event driven procedure for calculating enddate.
Can a Form be set up to allow multiple record inputs via a scrolling screen as described above and if so how?
Also in my early days with Access (some time ago!, haven't used it a lot recently) there were difficulties in multi Users accessing the same database and tables within it
Can it now be restricted to allow multi viewing but only one updating over a network, and again if so how?

Thanks a million for all your help

[Thomadh]

MMCCARTHY

Thanks so much , works great!

Can I ask a couple more things (you are so good!)

1. My use of Forms has been to display and input one record on screen at a time.
Given that effectively I am looking to build a Microsoft Project type task list via Access, it would be useful to see all preceding tasks on screen at input time (i.e. as in MS Project, Excel or Access Datasheet view), but still using the event driven procedure for calculating enddate.
Can a Form be set up to allow multiple record inputs via a scrolling screen as described above and if so how?
Also in my early days with Access (some time ago!, haven't used it a lot recently) there were difficulties in multi Users accessing the same database and tables within it
Can it now be restricted to allow multi viewing but only one updating over a network, and again if so how?

Thanks a million for all your help

A further query please MMCCarthy

As well as above it seems that in calculating end date it is setting the end date as a weekend date whereas I only want it to fall on a weekday (e,g, startdate of 20/11/06 (a Monday) with noofdays =5 gives and enddate of 25/11/06 (a Saturday).
Also connected with this could you suggest a way to include the startdate as day 1, i.e. the end date in the example above would then be 24/11/06 not 25/11/06 as 20/11/06 would in fact be the first day of work on a task
Many thanks, much appreciated if you could help with this...almost there!
Thomadh

[MMcCarthy]

In the form properties window under the format tab. Change the Default View to Continuous Forms.



In the Tools - Options window go to the Advanced tab. There are some options in there for record locking. Standard practice is to set the db to Shared and the default record locking to edited record. This will prevent any user from editing a record currently being edited by another user. Make sure the Open databases using record level locking option is ticked. You can also set the default record locking to all records.

[MMcCarthy]

As well as above it seems that in calculating end date it is setting the end date as a weekend date whereas I only want it to fall on a weekday (e,g, startdate of 20/11/06 (a Monday) with noofdays =5 gives and enddate of 25/11/06 (a Saturday).

Also connected with this could you suggest a way to include the startdate as day 1, i.e. the end date in the example above would then be 24/11/06 not 25/11/06 as 20/11/06 would in fact be the first day of work on a task

change the code to return a date instead of a number and to handle both of these issues.

 
Public Function CountDays(StartDate As Date, NoOfDays As Integer) As Date
' 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
	
	tmpDate = Weekday(([StartDate] - 1) + tmpNo)
	
	If tmpDate = 1 Then ' if sunday
		tmpDate = tmpDate + 1
	ElseIf tmpDate = 7 Then ' if saturday
		tmpDate = tmpDate + 2
	End If
	
	CountDays = tmpDate
 
End Function

Now change the update statement:


UPDATE Table1 SET Table1.EndDate = CountDays([StartDate],[NoOfDays]);

Change the code in the after update events as follows:

 
Private Sub StartDate_AfterUpdate()

   If Not IsNull(Me.NoOfDays) Then
	  Me.EndDate = CountDays(Me.StartDate, Me.NoOfDays)
	   End If

End Sub

Private Sub NoOfDays_AfterUpdate()

   If Not IsNull(Me.StartDate) Then
	  Me.EndDate = CountDays(Me.StartDate, Me.NoOfDays)
   End If

End Sub

[Thomadh]

change the code to return a date instead of a number and to handle both of these issues.

 
Public Function CountDays(StartDate As Date, NoOfDays As Integer) As Date
' 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
	
	tmpDate = Weekday(([StartDate] - 1) + tmpNo)
	
	If tmpDate = 1 Then ' if sunday
		tmpDate = tmpDate + 1
	ElseIf tmpDate = 7 Then ' if saturday
		tmpDate = tmpDate + 2
	End If
	
	CountDays = tmpDate
 
End Function

Now change the update statement:


UPDATE Table1 SET Table1.EndDate = CountDays([StartDate],[NoOfDays]);

Change the code in the after update events as follows:

 
Private Sub StartDate_AfterUpdate()

   If Not IsNull(Me.NoOfDays) Then
	  Me.EndDate = CountDays(Me.StartDate, Me.NoOfDays)
	   End If

End Sub

Private Sub NoOfDays_AfterUpdate()

   If Not IsNull(Me.StartDate) Then
	  Me.EndDate = CountDays(Me.StartDate, Me.NoOfDays)
   End If

End Sub

MMCCARTHY

Almost there and thanks, but all end dates are now calculating as dstes in January 1900.

Can you suggest why that is?

Hope you can suggest a reason

Many thanks

Thomadh

[MMcCarthy]

MMCCARTHY


Almost there and thanks, but all end dates are now calculating as dstes in January 1900.

Can you suggest why that is?

Hope you can suggest a reason

Many thanks

Thomadh

This sounds like a formatting problem with your original fields. What format are they set to?

[Thomadh]

This sounds like a formatting problem with your original fields. What format are they set to?

All sorted now.

Huge thanks!

[MMcCarthy]

you're welcome

All sorted now.

Huge thanks!

[Santiagoa]

Open the form in design view.
Open the properties window.
Click on the control for the StartDate field.
In the Event tab go to After Update
Click on the little arrow to the right and scroll to [Event Procedure] and select it.
Now click on the little button to its right with the dots on it.
The VB editor will open with the following.

Private Sub StartDate_After Update()

End Sub

You need to add the code so it looks like the following:

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

Now do exactly the same thing on the NoOfDays field. The code will be slightly different. See below:

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

I have a similar situation, but my form has a start date and I want to use a constant number of days to complete. I tried setting the attribute daysToComplete in the table with 5 as a default but does not work. Is there any way I can get to calculate the end date without haveng to enter the DaysToComplete value manually?

Thank you