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…
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?