By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,237 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

Calculate end date (working day) in Access

P: 11
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?
Oct 20 '06 #1
Share this Question
Share on Google+
16 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function CountDays(startDate As Date, NoOfDays As Integer) As Date 
  3. Dim tmpDate As Date
  4. Dim i As Integer
  5.  
  6. tmpDate = StartDate
  7. i=0
  8. Do Until i = NoOfDays
  9.   If WeekDay(tmpDate)<>1 Or WeekDay(tmpDate)<>7 Then
  10.     i = i+1
  11.     tmpDate=tmpDate+1
  12.   End If
  13. Loop
  14.  
  15. CountDays=tmpDate
  16.  
  17. End Function
  18.  
  19.  
Then run an update query as follows:

UPDATE TableName SET endDate=CountDays(startDate,NoOfDays);
Oct 20 '06 #2

P: 11
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function CountDays(startDate As Date, NoOfDays As Integer) As Date 
  3. Dim tmpDate As Date
  4. Dim i As Integer
  5.  
  6. tmpDate = StartDate
  7. i=0
  8. Do Until i = NoOfDays
  9.   If WeekDay(tmpDate)<>1 Or WeekDay(tmpDate)<>7 Then
  10.     i = i+1
  11.     tmpDate=tmpDate+1
  12.   End If
  13. Loop
  14.  
  15. CountDays=tmpDate
  16.  
  17. End Function
  18.  
  19.  
Then run an update query as follows:

UPDATE TableName SET endDate=CountDays(startDate,NoOfDays);
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,noofdays 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
Oct 21 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function CountDays(startDate As Date, NoOfDays As Integer) As Integer
  3. ' Function to count no of working days
  4. Dim tmpNo As Integer
  5. Dim tmpDate As Date
  6. Dim i As Integer
  7.     tmpNo = NoOfDays
  8.     tmpDate = startDate
  9.  
  10.     i = 0
  11.     Do Until i = NoOfDays
  12.         If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
  13.             tmpNo = tmpNo + 1
  14.         Else
  15.             i = i + 1
  16.         End If
  17.         tmpDate = tmpDate + 1
  18.     Loop
  19.  
  20.     CountDays = tmpNo
  21.  
  22. End Function
  23.  
  24.  
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.
Oct 21 '06 #4

P: 11
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function CountDays(startDate As Date, NoOfDays As Integer) As Integer
  3. ' Function to count no of working days
  4. Dim tmpNo As Integer
  5. Dim tmpDate As Date
  6. Dim i As Integer
  7.     tmpNo = NoOfDays
  8.     tmpDate = startDate
  9.  
  10.     i = 0
  11.     Do Until i = NoOfDays
  12.         If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
  13.             tmpNo = tmpNo + 1
  14.         Else
  15.             i = i + 1
  16.         End If
  17.         tmpDate = tmpDate + 1
  18.     Loop
  19.  
  20.     CountDays = tmpNo
  21.  
  22. End Function
  23.  
  24.  
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!
Oct 21 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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_AfterUpdate()

End Sub

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub StartDate_AfterUpdate()
  3.     If Not IsNull(Me.NoOfDays) Then
  4.         Me.EndDate = Me.StartDate + CountDays(Me.StartDate, Me.NoOfDays)
  5.     End If
  6. End Sub
  7.  
  8.  
Now do exactly the same thing on the NoOfDays field. The code will be slightly different. See below:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub NoOfDays_AfterUpdate()
  3.     If Not IsNull(Me.StartDate) Then
  4.         Me.EndDate = Me.StartDate + CountDays(Me.StartDate, Me.NoOfDays)
  5.     End If
  6. End Sub
  7.  
  8.  
Oct 21 '06 #6

P: 11
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_AfterUpdate()

End Sub

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub StartDate_AfterUpdate()
  3.     If Not IsNull(Me.NoOfDays) Then
  4.         Me.EndDate = Me.StartDate + CountDays(Me.StartDate, Me.NoOfDays)
  5.     End If
  6. End Sub
  7.  
  8.  
Now do exactly the same thing on the NoOfDays field. The code will be slightly different. See below:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub NoOfDays_AfterUpdate()
  3.     If Not IsNull(Me.StartDate) Then
  4.         Me.EndDate = Me.StartDate + CountDays(Me.StartDate, Me.NoOfDays)
  5.     End If
  6. End Sub
  7.  
  8.  
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
Oct 22 '06 #7

P: 11
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
Oct 22 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 22 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534

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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function CountDays(StartDate As Date, NoOfDays As Integer) As Date
  3. ' Function to count no of working days
  4. Dim tmpNo As Integer
  5. Dim tmpDate As Date
  6. Dim i As Integer
  7.     tmpNo = NoOfDays
  8.     tmpDate = StartDate
  9.  
  10.     i = 0
  11.     Do Until i = NoOfDays
  12.         If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
  13.             tmpNo = tmpNo + 1
  14.         Else
  15.             i = i + 1
  16.         End If
  17.         tmpDate = tmpDate + 1
  18.     Loop
  19.  
  20.     tmpDate = Weekday(([StartDate] - 1) + tmpNo)
  21.  
  22.     If tmpDate = 1 Then ' if sunday
  23.         tmpDate = tmpDate + 1
  24.     ElseIf tmpDate = 7 Then ' if saturday
  25.         tmpDate = tmpDate + 2
  26.     End If
  27.  
  28.     CountDays = tmpDate
  29.  
  30. End Function
  31.  
  32.  
Now change the update statement:


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

Change the code in the after update events as follows:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub StartDate_AfterUpdate()
  3.  
  4.    If Not IsNull(Me.NoOfDays) Then
  5.       Me.EndDate = CountDays(Me.StartDate, Me.NoOfDays)
  6.        End If
  7.  
  8. End Sub
  9.  
  10. Private Sub NoOfDays_AfterUpdate()
  11.  
  12.    If Not IsNull(Me.StartDate) Then
  13.       Me.EndDate = CountDays(Me.StartDate, Me.NoOfDays)
  14.    End If
  15.  
  16. End Sub
  17.  
  18.  
  19.  
Oct 22 '06 #10

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function CountDays(StartDate As Date, NoOfDays As Integer) As Date
  3. ' Function to count no of working days
  4. Dim tmpNo As Integer
  5. Dim tmpDate As Date
  6. Dim i As Integer
  7.     tmpNo = NoOfDays
  8.     tmpDate = StartDate
  9.  
  10.     i = 0
  11.     Do Until i = NoOfDays
  12.         If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
  13.             tmpNo = tmpNo + 1
  14.         Else
  15.             i = i + 1
  16.         End If
  17.         tmpDate = tmpDate + 1
  18.     Loop
  19.  
  20.     tmpDate = Weekday(([StartDate] - 1) + tmpNo)
  21.  
  22.     If tmpDate = 1 Then ' if sunday
  23.         tmpDate = tmpDate + 1
  24.     ElseIf tmpDate = 7 Then ' if saturday
  25.         tmpDate = tmpDate + 2
  26.     End If
  27.  
  28.     CountDays = tmpDate
  29.  
  30. End Function
  31.  
  32.  
Now change the update statement:


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

Change the code in the after update events as follows:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub StartDate_AfterUpdate()
  3.  
  4.    If Not IsNull(Me.NoOfDays) Then
  5.       Me.EndDate = CountDays(Me.StartDate, Me.NoOfDays)
  6.        End If
  7.  
  8. End Sub
  9.  
  10. Private Sub NoOfDays_AfterUpdate()
  11.  
  12.    If Not IsNull(Me.StartDate) Then
  13.       Me.EndDate = CountDays(Me.StartDate, Me.NoOfDays)
  14.    End If
  15.  
  16. End Sub
  17.  
  18.  
  19.  
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
Oct 23 '06 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Oct 23 '06 #12

P: 11
This sounds like a formatting problem with your original fields. What format are they set to?
All sorted now.

Huge thanks!
Oct 24 '06 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
you're welcome



All sorted now.

Huge thanks!
Oct 25 '06 #14

P: 18
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_AfterUpdate()

End Sub

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub StartDate_AfterUpdate()
  3.     If Not IsNull(Me.NoOfDays) Then
  4.         Me.EndDate = Me.StartDate + CountDays(Me.StartDate, Me.NoOfDays)
  5.     End If
  6. End Sub
  7.  
  8.  
Now do exactly the same thing on the NoOfDays field. The code will be slightly different. See below:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub NoOfDays_AfterUpdate()
  3.     If Not IsNull(Me.StartDate) Then
  4.         Me.EndDate = Me.StartDate + CountDays(Me.StartDate, Me.NoOfDays)
  5.     End If
  6. End Sub
  7.  
  8.  
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
Jan 31 '07 #15

P: 3
Hello all..

Im trying to get 'mmccarthy's' first post on this thread to work.
In my case, my Form contains a 'StartDate' , an 'EndDate' , and an 'EligibilityDate'.

I need the date value in 'EligibilityDate' to be 120 days forward from 'EndDate'.

(one purpose of the form is to track each employees R&R which reoccures each 120 days from the end of their prior R&R.)

I'm a Systems Engineer with very little code experience and I have been hacking away at this for 3 days. Hate to be a bother, but I can't make it work. I got it to work one time using the update query.. but I need the calculation for 'EligibilityDate' to fire off on eventupdate, or something. Can one of you please steer my along? I'm a little outside of my realm, here.
Sep 15 '07 #16

Scott Price
Expert 100+
P: 1,384
Hello all..

Im trying to get 'mmccarthy's' first post on this thread to work.
In my case, my Form contains a 'StartDate' , an 'EndDate' , and an 'EligibilityDate'.

I need the date value in 'EligibilityDate' to be 120 days forward from 'EndDate'.

(one purpose of the form is to track each employees R&R which reoccures each 120 days from the end of their prior R&R.)

I'm a Systems Engineer with very little code experience and I have been hacking away at this for 3 days. Hate to be a bother, but I can't make it work. I got it to work one time using the update query.. but I need the calculation for 'EligibilityDate' to fire off on eventupdate, or something. Can one of you please steer my along? I'm a little outside of my realm, here.
Hello mfetterhoff,

Please do not hijack another member's thread with your own question. Even when the thread is old, this confuses the issue... The better practice is to post your own thread with a link to the older thread containing related information.

In your new thread please post the code you are using, including any changes to what was posted earlier in this (older) thread. Do so and we will be glad to help you with your issue.

Regards,
Scott
Sep 15 '07 #17

Post your reply

Sign in to post your reply or Sign up for a free account.