Solved

How populate date field with a calculated date

Posted on 2011-03-08
15
370 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
  • 7
  • 6
  • 2
15 Comments
 
LVL 39

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 39

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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now