Calculate Due Date from Previous Record Close Date

eyeisystems
eyeisystems used Ask the Experts™
on
Hi,

I have an access database whereby i import common tasks into a tasks table where they are linked to the current project.

Each task has a start date, end date and due date and a process order number.

What i want to do is create a macro/query/vba?! so that as the tasks are imported the lowest process number gets a start date of today i.e. now () and when that task is closed the next process order number in line gets a start date of the previous process number's end date.

As the common tasks are optional, it could be that the process number increments jump e.g. 1,2,4,5,6,8,10 etc.

Any ideas

Many thanks

Brendan
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
So you can "order" the records by that "process number"?

Each task has a start date, end date and due date and a process order number.
Are those fields filled in when you import? That would be somewhat odd, but it's hard to tell from your question.

Instead, it's common that you would import Tasks associated with a Project (and relate them to that Project). Then, as you complete a Task (and enter an End Date value) you could then stamp the "next" Task with the Start Date, based on that End Date.

This is most easily done in the Form where you modify the Task. You could run code like this, perhaps in the AfterUpdate event of the Form:

'/ make sure an End Date is available
If Nz(Me.YourEndDate,0) <>0 Then
  '/ get the next ProcessNumber
  Dim ProcessNum As Double
  ProcessNum = DMin("ProcessNumber", "TaskTables", "EndDate IS NOT NULL")
  Currentdb.Execute "UPDATE TasksTable SET StartDate=#" & Me.YourEndDate & "# WHERE TaskID="         & Me.TaskID & " AND ProcessNumber=" & ProcessNum
End If

Obviously this is air-code, and you'd have to change the Table, Field and Control names to match your own.

Author

Commented:
Many thanks!

You're correct, the common tasks that are Appended to the task table have no dates associated at the point of import and are just updated as tasks are progressed.

Also, how would I set the lowest process number that's imported  to have today's date? I can't see that from the code unless I'm missing something?

I'll give it a shot though and feedback, currently 00:45 here in the UK so will be in the morning.

Thanks again!

Brendan
Top Expert 2011

Commented:
Brendan,

Scott's code is for a form's after update event for an end date to set teh next task's start date.

I am not sure how you import the records ,  One solution would be to run code after the import is competed to add today's date to each record that has the smallest process number.

Difficult to give you more specific details without knowing more about the database and the import process.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi Boyd,

Essentially it's the access desktop project management with a couple of extra columns added to the tasks and common tasks tables.

To give you an idea though..

There is a common tasks table/sub form, a tasks table/sub form and a project table/form.

The user opens the project form and selects a button which opens the common tasks sub form, the user can then select the relevant common tasks to add to the task table, essentially there's a Boolean yes/no column they can select for each task they want to import. Once selected they then press the add selected tasks button which runs an append query to add the common tasks details to the task table along with the current project ID.

Hope that makes sense.

Best regards

Brendan

Author

Commented:
To make things more complicated the user can go in at a later date and append more common tasks so by doing that it would need to make sure it doesn't affect the already imported tasks.

Best regards

Brendan

Author

Commented:
Hi,

I've tried the code as above in my Form, however i'm getting a type mismatch on the "Date Completed" is not null, i've also tried is not blank with no luck. Any suggestions? Many thanks Brendan

Private Sub Form_AfterUpdate()
'/ make sure an End Date is available
If Nz(Me.[Date Completed], 0) <> 0 Then
  '/ get the next ProcessNumber
  Dim ProcessNum As Double
  ProcessNum = DMin("Tasks", "Process Order", "Date Completed" Is Not Null)
  CurrentDb.Execute "UPDATE Tasks SET [Start Date]=#" & Me.[Date Completed] & "# WHERE [ID]=" & Me.[ID] & " AND [Process Order]=" & ProcessNum
End If
End Sub

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Commented:
The DMin line is incorrect. Assuming "Tasks" is the name of the Table, and "Process Order" is the name of the Field you want to return:

ProcessNum = DMin("Process Order", "Tasks", "[Date Completed] Is Not Null")

You could also try:

ProcessNum = DMin("Process Order", "Tasks", "Nz([Date Completed],0) <> 0")

Basically, that line looks for the "minimum" value in Tasks.[Process Order] where there is a value in [Date Completed]

user can go in at a later date and append more common tasks so by doing that it would need to make sure it doesn't affect the already imported tasks.
Assuming the new tasks would be added on to the "end" of the other Tasks, you shouldn't have a problem. The Form's After_Update code would still work (if that's what you end up using).

If you try to intermingle tasks you'll have no end of trouble. For example, lets say you have have Tasks 1, 2, 5, 8 and 9, and you've "completed" up to 8. You then try to add Task 3 and 4 between two completed tasks - which would mean that the Dates for those would very likely be off in consideration of the previous Tasks.

So as Boyd said, you really need to give us more information as to exactly what you're trying to do, and let us know the different scenarios you could run into. We could give much better advice from there.

Author

Commented:
Hi Scott,

That line fixed the issue although it just seems to be updating the start date of the current record rather than the next process number in the line.

Essentially the user will create a project and select common tasks that are apporiate for the project, they are linear so they should never jump from say 1 - 3,4 down to 2, however because some are optional in later processes they need the ability to decide if they need to add common task process order 10 instead of 11 etc.

I'm hoping that clarifies it a little? :/

Cheers

Brendan
Most Valuable Expert 2012
Top Expert 2014
Commented:
You'd have to modify this line to point to the "next" record:

CurrentDb.Execute "UPDATE Tasks SET [Start Date]=#" & Me.[Date Completed] & "# WHERE [ID]=" & Me.[ID] & " AND [Process Order]=" & ProcessNum

Without knowing your table structure it's hard to say exactly what needs to be modified, but I'd think you should point to the "TaskID" (i.e. the PARENT of that record) and not the ID value (which generally defines the specific record you're pointing to in that dataset).

So if my "parent" record field is named TaskID, the statement would look like:

CurrentDb.Execute "UPDATE Tasks SET [Start Date]=#" & Me.[Date Completed] & "# WHERE [ID]=" & Me.[TaskID] & " AND [Process Order]=" & ProcessNum

This assumes the DMIN process is getting the correct value, of course. You can verify that by putting this line immediately after the DMIN line:

Msgbox ProcessNum

You should then see a message box that shows the value retrieved by the DMIN process.

Author

Commented:
Hi,

I think thats the issue, its returning '1', presumably it should be returning '2' as the next process number?

Author

Commented:
Realised why it was returning '1' because the is NOT null needed to be 'is null'

just to work out the adding to the next start date now, will report back :)

Many thanks

Brendan

Author

Commented:
Perfect!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial