Returning the next date from a series of date fields?

  • Thread starter Thread starter Ron
  • Start date Start date
R

Ron

My Access database contains several date fields that
indicate due dates for various tasks. I want to build a
query that will tell me for each record what the next due
date is. For example, if I have three date fields, Task1,
Task2, and Task3, and the values in my fields for record 1 are:

Task1: 9/1/04
Task2: 8/25/04
Task3: 9/15/04

and todays date is 8/13/04, I want an expression in my
query that will give me 8/25/04, which is the next date in
my series of fields.

I did this once before in Excel using, among other things,
the Rank function. Access doesn't seem to have anything
equavent to Rank.

Can anyone suggest a solution?

Thanks,
Ron
 
Ouch, the best way to handle this situation would be to move this repeating data
into a separate table that would contain the ProjectID (or whatever you use to
define the uniqueness of each row), the task id and the due date. Then the
query would be simple. As well as adding more dates and tasks to the project(?)
would be simple.

SELECT ProjectID, Min(DueDate)
FROM TaskDueTable
WHERE DueDate > Date()
GROUP BY ProjectID, TaskID

IF you have to live with the current design, you might be able to use a
calculated column that would look something like the following (all on one line).

IIF(DateDiff("D",Date(),Task1)>0,Task1,
IIF(DateDiff("D",Date(),Task2)>0,Task2,
IIF(DateDiff("D",Date(),Task3)>0,Task3,Null)))

Of course, if you have more than three dates or some of them are null or they
aren't in ascending order task1 ends before task2 which ends before task3, you
will have errors.

You could write a custom VBA function to return the minimum date greater than a
specified date. That might look something like this UNTESTED AIRCODE.

Public Function getNextTaskDate(CutoffDate, _
ParamArray Values()) as Variant
Dim iLoop as integer
Dim vReturn as Variant

if IsDate(CutOffDate) = false then CutoffDate = Date()

FOR ILoop = LBound(ParamArray) to UBound(ParamArray)
If IsDate(ParamArray(iLoop)) Then
If ParamArray(iLoop) > CutOffDate then
If isNull(vReturn) Then
vReturn = ParamArray(ILoop)
ElseIf vReturn < ParamArray(ILoop) Then
vReturn = ParamArray(iLoop)
End if

End if
End if
Next Iloop

getNextTaskDate = vReturn

End Function
 
Back
Top