Earliest Date

[DannyBoy]

Each record in my query has 6 date fields - D1, D2, D3
etc. I would like to define a new field to be
called "D_earliest" that will be the earliest of the 6
date fields. The earliest date can be located in any of
the 6 possible date fields D1 through D6.

I should also add that each record does always contain
the 6 date fields. Some records contain only 3 date
fields (D1, D3, D5), with the other date fields being
null values. I want to define the "D_earliest" field to
pick the earliest of the available date fields.

I hope this makes sense and would greatly appreciate any
assistance offered. Many thanks.

 

[chris]

You could try a complex nested IIF approach

 

[John Spencer (MVP)]

Looks as if you have a structural problem with your tables; however you could
use a Maximum function to return the Maximum date.

Here is one that might work for you. Save it to a module and then call it like
so in your query.

Field: LastDate: fnMax(D1,D2,D3,D4,D5,D6)


Public Function fnMax(ParamArray varValue() As Variant)
Dim varMax As Variant
Dim intIndex As Integer

On Error GoTo fnMax_Err
varMax = -1E+20
For intIndex = 0 To UBound(varValue())
If varValue(intIndex) > varMax Then
varMax = varValue(intIndex)
End If
Next intIndex

If varMax = -1E+20 Then
fnMax = Null
Else
fnMax = varMax
End If

fnMax_Exit:
Exit Function

fnMax_Err:
Select Case Err.Number
Case 0
Case Else
MsgBox "Error " & Err.Number & ": " & _
Err.Description & vbCrLf & vbCrLf & _
"(Programmer's note: vbaMathematics.fnMax)" _
& vbCrLf, _
vbOKOnly + vbCritical, "Run-time Error!"
End Select
Resume fnMax_Exit
End Function

 

[Daniel Attard]

Thank you for posting this code. I think this will work
for me. The only problem is: the code you provided
locates the "maximum" date, but what i am trying to
identify is the "minimum" date. Can you please show me
the necessary changes to be able to identify to "minimum"
date? Thanks again for your assistance.

 

[Daniel Attard]

I've managed to rework your code to find the Minimum date
rather than Maximum date. The only problem, however, is
that using (ParamArray varValue() As Variant) results in
my function returning a Variant data type, not a Date
type. I need to be able to sort by the resulting Minimum
dates which I cannot do on a Variant. Any ideas? I
truly appreciate it. Thanks.

 

[John Spencer (MVP)]

Probably the following UNTESTED code would do it.

Public Function fnMin(ParamArray varValue() As Variant)
Dim varMax As Variant
Dim intIndex As Integer

On Error GoTo fnMax_Err
varMax = 1E+20
For intIndex = 0 To UBound(varValue())
If varValue(intIndex) < varMax Then
varMax = varValue(intIndex)
End If
Next intIndex

If varMax = 1E+20 Then
fnMin = Null
Else
fnMin = varMax
End If

fnMin_Exit:
Exit Function

fnMin_Err:
Select Case Err.Number
Case 0
Case Else
MsgBox "Error " & Err.Number & ": " & _
Err.Description & vbCrLf & vbCrLf & _
"(Programmer's note: vbaMathematics.fnMax)" _
& vbCrLf, _
vbOKOnly + vbCritical, "Run-time Error!"
End Select
Resume fnMin_Exit
End Function

 

[John Spencer (MVP)]

Why can't you sort on the dates?

Try wrapping the return value in CDate(Nz(FnMin(FldA,FldB,FldC,FldD,...)))

 

[DannyBoy]

Thank you John. Once I wrapped the return value with the
CDate(Nz(fnMin function that you gave me, it worked like
a charm. How can I ever thank you enough!!