Latest date from different date fields?

[Big Pat]

I have a table which has ten different date fields, relating to when certain parts of a process were completed. I know it *ought* to be a separate table related to the existing one, but this is a project I've taken over from someone else and there are so many other things reliant on it that I don't want to mess with it too much.

Some or all of the dates may be missing (i.e. not completed yet) and I need to work out what is the latest date that appears for a given record. The dates are not necessarily in order, e.g. date 3 may actually be later than date 7 etc. etc.

So, I was hoping for something along the lines of a Max or DMax function, kind of like Max([Date1],[Date2],.....[Date10]) but of course that doesn't work.

How should I go about this?

Thanks,

 

[Big Pat]

Actually I have come up with something using 9 IIF statements such as

Round1: IIf([date2]>[date1],[date2],[date1])
Round2: IIf([date3]>[Round1],[date3],[Round1])
etc
etc
Latest date: IIf([date10]>[Round8],[date10],[Round8])

so it's like a knockout competition where the latest date "so far" goes up against the next date and the later date wins out. So the winner of round 9 is the latest of the lot.

That seems to work OK, but obviously I'd like to know if there's a better way.

Thanks,

 

[jdraw]

Can you tell us a little bit about the fields in your Table? Structure etc.
You could make a function of your own.

 

[Big Pat]

Well, as far as this table is concerned, there's not a great deal to tell. Various hospital departments are taking part in various clinical trials. Each trial needs several preliminary steps "signed off" before it's allowed to proceed. And the ten date fields relate to the dates these steps have been signed off by various people/bodies.

So you have things like: ethical approval, clinical approval, pharmacy agreement, funder contract etc. Depending on the type of trial, some will need only one or two sign-offs, some will need many more. And they can happen in (almost) any sequence.

I need to find out if there are any trials that have not hand any sign-offs in the last 10 days, 30 days etc. To do that, I forst need to know which was the latest sign-off and when.

The rest of the table is mainly text-fields detailing the name of the trial, the clinical field it relates to (orthopaedics, cardiology, etc.) and which person in the Research office is responsible for chasing things up.

This is probably the one query where I will need this, because I intend to base a report on the query so the manager can easily track what the hold-ups are.

Thanks.

 

[MMaynard]

You could load the date fields to an array, then transfer the array to a disconnected recordset and sort it.

Dim TempRS As ADODB.Recordset
Dim TempArray As Variant
Set TempRS = New ADODB.Recordset
With TempRS.Fields
    .Append "tempDate", adDBDate
End With
TempArray = Array(Me.Field1.Value, Me.Field2.Value, Me.Field3.Value, Me.Field4.Value, Me.Field5.Value, Me.Field6.Value, Me.Field7.Value, Me.Field8.Value, Me.Field9.Value, Me.Field10.Value)
With TempRS
    .Open
    .AddNew "TempDate", TempArray(0)
    .AddNew "TempDate", TempArray(1)
    .AddNew "TempDate", TempArray(2)
    .AddNew "TempDate", TempArray(3)
    .AddNew "TempDate", TempArray(4)
    .AddNew "TempDate", TempArray(5)
    .AddNew "TempDate", TempArray(6)
    .AddNew "TempDate", TempArray(7)
    .AddNew "TempDate", TempArray(8)
    .AddNew "TempDate", TempArray(9)
End With
TempRS.Sort = "Tempdate DESC"

TempRS.MoveFirst
Me.topDate = TempRS.Fields(0).Value
TempRS.Close
Set TempRS = Nothing

 

[Big Pat]

Hi

Thanks for this. I dont really understand arrays, I'm afraid, but if I follow this correctly, it would create a "kind of table" and then write each data into separate records in that table, with a "field name" of TempDate. I'm visualising something like the Paste Special ->Transpose function in Excel, where you can copy a horizontal range of cells and paste them vertically (or vice versa). And then you sort it by that field. I know I'm probably thinking of this in all the wrong terms, but is that the gist of it?

Is there any benefit to doing it that way, rather than my nine IIFs?

For instance, would there be a way to save that array and create a REAL table from it? I'm not certain that I would want to do this of course, as it would contain only one original record, but you've got me wondering.

 

[MMaynard]

No need to have a real table, the result of the code above is in the line

Me.topDate = TempRS.Fields(0).Value

That example is tied to a field on the form I was testing with, you can just put the above code in the afterupdate event of the form, or make it a function and tie it to a button.