Solved

How populate date field with a calculated date

Posted on 2011-03-08
15
375 Views
Last Modified: 2012-05-11
The best way I could think of to explain what I am trying to do was to create a “demo” database (see attached).  Here is the challenge…

1)Open “frmProject”
2)Double-click the one record in the listbox.
3)A second form should open.  In this 2nd form enter a date in the field, “Job Due Date”.
4)Here is the issue… the field “Task 1 Due Date” should auto populate with a date that is a calculation based on “Job Due Date” MINUS the value in table “tblProjTypes”, “Task1Days”.  In this case 3.  This also has to be connected to the project type, in this case “Project Type 1”.  If it was Project Type 2, then the calculation would be “Job Due Date” minus 5.
5)Please note that this calculation has to use week days so if the “Job Due Date” is a Monday, then the populated date in “Task 1 Due Date” should be the Friday before.
6)Also, if at all possible, I would like the date to be calculated using week days only PLUS taking holidays into consideration.  So using the example above, if the Friday was a holiday, then the Thursday before would be the answer.

How can all of this be accomplished?

--Steve

Test-Date-Insert.zip
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 
LVL 40

Expert Comment

by:als315
ID: 35070436
1. You don't need to store values which could be calculated in a table, until calculation tooks a lot of time.
2. You can use this function everywhere to calculate new date:
Function TDD(d1 as date, dd as long) as date
' d1 - Job Due Date, dd - Task1Days
Dim D As Date
D =  DateAdd("d", -dd, d1)
While Weekday(D) = vbSunday Or Weekday(D) = vbSaturday
    D = DateAdd("d", -1, D)
Wend
TDD = D
End Function
3. There are no ready functions for holidays (and they are different in different countries), you can add conditions for these dates to this function
0
 

Author Comment

by:SteveL13
ID: 35070854
I'm sorry I don't fully nuderstand...  where do I put the code you provided?
0
 
LVL 40

Expert Comment

by:als315
ID: 35071061
Function should be inserted in module (Create-Module)
Then you can use it in a form, query, report.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:JAMcDo
ID: 35071456
My attempt:

To handle Statutory Holidays, you need to create another table listing the holiday dates.  I created a table tblStatHolidays with the important field being StatHoliDate as short date.

In the form frmProjDetail, for the text158 (your name for the 'JobDueDate') create and After Update event and put in this code:

    txtTask1DueDate = RtnDueDate(Text158, txtProjType)
    Form.Refresh

Create a Module with the function shown below:

Public Function RtnDueDate(GDate As Date, PType As String) As Date
    Dim DaysBefore As Integer
    Dim RDD As Date
    DaysBefore = DLookup("[Task1Days]", "tblProjectTypes", "[ProjType] = '" & PType & "'")
    RDD = GDate - DaysBefore
    For i = 1 To DaysBefore 'Count backwards, checking each day for weekend day or holiday and ignoring if true
        RDD = RDD - 1
        Do While Weekday(RDD) = 1 Or Weekday(RDD) = 7 Or IsNull(DLookup("[StatHoliDate]", "tblStatHolidays", "[StatHoliDate] = #21 Feb 2011#")) = True
            RDD = RDD - 1
        Loop
    Next
    RtnDueDate = RDD
 End Function

My code counts back the number weekdays ignoring weekend days and holidays.  If you only want to count back ignoring weekends, use the code provided by als315.

As als315 says, calculated fields (generally) do not have to be stored.

I have attached you DB with my ammendments for your perusal/benefit.

J.
 Test-Date-Insert.accdb
0
 

Author Comment

by:SteveL13
ID: 35072066
To als315:  I copied the module code in the test database.  But I do not know what to do to make the calculated date show up in the second date field.
0
 

Author Comment

by:SteveL13
ID: 35072928
So far nothing is working so I'm taking a different approach.  Using the sample database provided I'm using the following after update event for "txt158" which is calculating the date backwards from the "txtTask1DueDate" but I want it to adjust for weekends.  What do I need to do to this syntax to make that happen?

Me.txtTask1DueDate = (Me.Text158 - DLookup("Task1Days", "tblprojecttypes", "[ProjType] = txtProjType"))

--Steve
0
 
LVL 3

Expert Comment

by:JAMcDo
ID: 35073272
Clarification please:

If Monday, 07 March 2011 was the date in Text158, what is the desired answer?  If the correct answer is:
- Friday, 04 Mar 2011, then use als315's function
- Thursday, 03 Mar 2011, then neither proposed function
- Wednesday, 02 Mar 2011, then use my function.

To remove the Holiday lookup feature, REM (') out the code following
        Do While Weekday(RDD) = 1 Or Weekday(RDD) = 7

As an answer your last question, I think you need a function.  Using my function, try (I have not verified) this:

Me.txtTask1DueDate = RtnDueDate(Me.Text158, txtProjType)

J.

0
 

Author Comment

by:SteveL13
ID: 35073563
JAMcDo:  I am sorry I didn't see that you had sent the sample file back.  I appreciate it.  One thing though... if I enter 3/31/2011 it should return 3/28/2011.  Instead it is returning 3/23/2011.  ???

--Steve
0
 
LVL 3

Expert Comment

by:JAMcDo
ID: 35074278
Sorry - Coding error

In the function, change
-->      RDD = GDate - DaysBefore
to >      RDD = GDate


J.    
0
 

Author Comment

by:SteveL13
ID: 35074601
I made that change but it doesn't seem to be accounting for holidays.  Did I do something wrong?
0
 
LVL 3

Expert Comment

by:JAMcDo
ID: 35074802
You did not do anything wrong.  I did.  I had hard coded a holiday date of 21 Feb 2011 into the function.  BIG mistake.

Because there are now 2 changes from the originally posted function, I am reposting the entire function.

Public Function RtnDueDate(GDate As Date, PType As String) As Date

    Dim DaysBefore As Integer
    Dim RDD As Date
   
    DaysBefore = DLookup("[Task1Days]", "tblProjectTypes", "[ProjType] = '" & PType & "'")
   
    RDD = GDate
   
    For i = 1 To DaysBefore 'Count backwards, checking each day for weekend day or holiday and ignoring if true
        RDD = RDD - 1
        Do While Weekday(RDD) = 1 Or Weekday(RDD) = 7 Or Not IsNull(DLookup("[StatHoliDate]", "tblStatHolidays", "[StatHoliDate] = #" & RDD & "#"))
            RDD = RDD - 1
        Loop
    Next
   
    RtnDueDate = RDD
   
End Function

Note the change in the Do While statement.

Hope this does what you want,

J.
0
 

Author Comment

by:SteveL13
ID: 35083623
WOW!  This is absolutely perfect.  Now I find that we have a little change in the situation.  There are actually 3 dates that have to be autopopulated instead of one.  I've attached the test database again.  Can you write the necessary code to allow for this?
Test-Date-Insert.accdb
0
 
LVL 3

Expert Comment

by:JAMcDo
ID: 35084152
I don't understand when Task 2 and Task 3 due dates would filled in:  

Are they still based on 3 or 5 business day prior to the Job Due Date - the same as for Task 1 Due Date?  

Are Task 2 and Task 3 due dates based on an updated entry in Job Due Date?

Does Task 2 get filled if Task 1 Due Date has a value?  Similarly, does Task 3 get filled if Task 2 Due Date has a value?

J.
0
 

Author Comment

by:SteveL13
ID: 35084670
These 2 new date should auto fill exactly as the 1st one does thanks to your code.  But... Task Date 2 should backup from Job Due Date by x days and Task Date 3 should backup from Job Due Date by x days per the parameters as set in tblProjectTypes, Task2Days and Task3Days.

So, using 5/31/11 as a due date, Task 1 due date should be 5/19/11.  Task 2 due date should be 5/23/11.  Task 3 due date should be 5/26/11.  This is using 5/30 as a holiday and also accounting for weekends.  

Anytime a date is entered in Job Due Date, whether it be the first time entry or an updated entry, the above should auto fill.

--Steve
0
 
LVL 3

Accepted Solution

by:
JAMcDo earned 500 total points
ID: 35086386
Contained within the attached example database is amended code for the RtnDueDate function and on the form frmProjDetail for the After Udate event for Text158.

It seems to work for me.  Hope this is what you want.

The one thing I don't like the way the frmProjDetail works is that the user has to manually click on another field before the Text158 After Update kicks in.  (I use the built-in calender function to change the Text158 date.)  I tried a few things but no change.

J.
 Test-Date-Insert2.accdb
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question