435,264 Members | 1,237 Online
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
16 Replies

 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   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=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   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=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

 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   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. 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   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! Oct 21 '06 #5

 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   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: Expand|Select|Wrap|Line Numbers   Private Sub NoOfDays_AfterUpdate()     If Not IsNull(Me.StartDate) Then         Me.EndDate = Me.StartDate + CountDays(Me.StartDate, Me.NoOfDays)     End If End Sub     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   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: Expand|Select|Wrap|Line Numbers   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 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

 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

 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   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: Expand|Select|Wrap|Line Numbers   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       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   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: Expand|Select|Wrap|Line Numbers   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 Oct 23 '06 #11

 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

 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   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: Expand|Select|Wrap|Line Numbers   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 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