Trying to get greater of four date fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to analyze four date fields and return the most recent date in a
separate field. The four fields being looked at can have a combination of
all dates, all null values or dates and null values. Three examples (records)
are included.

There are about 3,000 records in the query - all of them need a "latest
date" or null value in the separate field.

Record 1 1/1/04, 2/28/04, 5/13/04, 3/12/04 result=5/13/04
Record 2 null, null, null, null result=null
Record 3 2/2/04, null, 3/18/02, null result=2/2/04

Thank you in advance for your help. :)
 
Paste the function below into a standard module (Modules tab of Database
window).

Then type something like this into a fresh column in the Field row of your
query:
MaxDate: Largest([Field1], [Field2], [Field3], [Field4])
substituting the names of your 4 date fields.


Public Function Largest(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMax As Variant 'Largest value found so far.

varMax = Null 'Initialize to null

For i = LBound(varValues) To UBound(varValues)
If IsNumeric(varValues(i)) Then
If varMax >= varValues(i) Then
'do nothing
Else
varMax = varValues(i)
End If
End If
Next

Largest = varMax
End Function
 
Back
Top