How populate date field with a calculated date

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

I'm sorry I don't fully nuderstand...  where do I put the code you provided?

Function should be inserted in module (Create-Module)
Then you can use it in a form, query, report.

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

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.

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

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.

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

Sorry - Coding error

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


J.    

I made that change but it doesn't seem to be accounting for holidays.  Did I do something wrong?

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.

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

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.

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