Select latest date to determine total duration

[Kayleen H via AccessMonster.com]

I need to determine total duration for a select assignment in a specified
record.

The record incorporates 4 requirements necessary to get an Assignment
approved with completion dates.
I need to determine the total duration to approval from the latest completion
date of the 4 requirements and the date the assignment is received. For
Example:

Date Assignment Received... 09/01/05

Requirement1 Completion 10/1/05
Requirement2 Completion 09/20/05
Requirement3 Completion 11/01/05
Requirement4 Completion 10/15/05

Need to determine duration time between Requirement3 (latest date) and Date
Received
OR if Requirement4 is the latest date, Requirement4 and Date Received
OR if Requirement1 is the latest date, Requirement1 and Date Received...etc

This is the only way this can be set up as there are multiple procedures for
each step that don't need to be detailed here. Just need to determine the
latest date among the 4 steps. Is there a way to do this?

I hope this makes sense....

Thank you.

 

[Guest]

Kayleen,

Can you provide more details in regards to the tables/columns you are using?
This would help in providing you a query statement that provides the
information you need.

- Ted

 

[Kayleen H via AccessMonster.com]

Only one table "Schedule".
Column Names are [DrSOWARcv], [DrIGEARcv], [FormAAComp], [COAssignAComp] and
[DateRqmtRcvd]
I need to be able to include the duration total in my form.

Thanks,

Kayleen,

Can you provide more details in regards to the tables/columns you are using?
This would help in providing you a query statement that provides the
information you need.

- Ted

I need to determine total duration for a select assignment in a specified
record.

[quoted text clipped - 24 lines]

Thank you.

 

[Guest]

Kathleen,

I am trying to tie your columns back to your original post. Can you verify
the following assumptions?

*Schedule
[DrSOWARcv]
[DrIGEARcv]
[FormAAComp] <- Requirement
[COAssignAComp] <- Assignment
[DateRqmtRcvd] <- Received Date

What I am expecting is you have one record with the assignment and its
received date. You then have subsequent records, in the same table, that
reflect received requirements and may or may not have a requirement completed
date filled out. For example:

Assignment | Recvd Date | Requirement | Completed Date
-----------------------------------------------------------------------
Assignment01 | 01/15/2005 | <null> | <null>
Assignment01 | ? | Req 1 | 01/30/2005
Assignment01 | ? | Req 2 | 02/20/2005
Assignment01 | ? | Req 3 | 02/28/2005
Assignment02 | 02/01/2005 | Req 1 | 02/02/2005
Assignment02 | ? | Req 1 | 02/02/2005

The query result would be something like:

Assignment | Received | Last Item Completed

Only one table "Schedule".
Column Names are [DrSOWARcv], [DrIGEARcv], [FormAAComp], [COAssignAComp] and
[DateRqmtRcvd]
I need to be able to include the duration total in my form.

Thanks,

Kayleen,

Can you provide more details in regards to the tables/columns you are using?
This would help in providing you a query statement that provides the
information you need.

- Ted

I need to determine total duration for a select assignment in a specified
record.

[quoted text clipped - 24 lines]

Thank you.

 

[Marshall Barton]

I need to determine total duration for a select assignment in a specified
record.

The record incorporates 4 requirements necessary to get an Assignment
approved with completion dates.
I need to determine the total duration to approval from the latest completion
date of the 4 requirements and the date the assignment is received. For
Example:

Date Assignment Received... 09/01/05

Requirement1 Completion 10/1/05
Requirement2 Completion 09/20/05
Requirement3 Completion 11/01/05
Requirement4 Completion 10/15/05

Need to determine duration time between Requirement3 (latest date) and Date
Received
OR if Requirement4 is the latest date, Requirement4 and Date Received
OR if Requirement1 is the latest date, Requirement1 and Date Received...etc

This is the only way this can be set up as there are multiple procedures for
each step that don't need to be detailed here. Just need to determine the
latest date among the 4 steps. Is there a way to do this?

If you have all four of the requirement fields in one
record, then I think you need a user defined function to
determine the Max date:

Public Function MaxOfList(ParamArray vValues() As Variant) _
As Variant
Dim vX As Variant
MaxOfList = vValues(0)
For Each vX In vValues
If vX > MaxOfList Then MaxOfList = vX
Next vX
End Function

Then your duration expression would look like:
DateDiff("d", Received, MaxOfList(req1, req2, req3, req4))