Calculate Due Date from Previous Record Close Date

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
eyeisystemsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
eyeisystemsAuthor 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
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
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.
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

eyeisystemsAuthor 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
eyeisystemsAuthor 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
eyeisystemsAuthor 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

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
eyeisystemsAuthor 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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eyeisystemsAuthor Commented:
Hi,

I think thats the issue, its returning '1', presumably it should be returning '2' as the next process number?
eyeisystemsAuthor 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
eyeisystemsAuthor Commented:
Perfect!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.