Avatar of Brendan
Brendan
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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
Microsoft AccessDatabasesProgrammingVBA

Avatar of undefined
Last Comment
Brendan

8/22/2022 - Mon
Scott McDaniel (EE MVE )

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.
Brendan

ASKER
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 MVP 2010-2015

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Brendan

ASKER
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
Brendan

ASKER
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
Brendan

ASKER
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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott McDaniel (EE MVE )

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.
Brendan

ASKER
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
ASKER CERTIFIED SOLUTION
Scott McDaniel (EE MVE )

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Brendan

ASKER
Hi,

I think thats the issue, its returning '1', presumably it should be returning '2' as the next process number?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Brendan

ASKER
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
Brendan

ASKER
Perfect!