Find max date on a form

[Box666]

I have 4 date boxes on a form, date boxes 1-3 are completed by the
operator, but I want date box4 to be the maximum of boxs 1-3 (but only
after all 3 boxes have been completed)

I have been able to do nothing until the 3 date boxes are completed,
but I cannot get it to choose the max date)

=IIf(IsNull([date1] And [date2] And [date3])," ",Max([date1] or [date2]
or [date3]))

Any help gratefully received

Bob

 

[John Spencer]

I would use the Switch function for this. You can do it with nested IIF
statements, but that becomes difficult for me to read.

SWITCH(IsNull(Date1) OR IsNull(Date2) Or IsNull(Date3), Null,
Date1>=Date2 and Date1>=Date3, Date1,
Date2>=Date3,Date2,
True,Date3)

Obviously, You can use your formulation to check for null, since null
propagates.
IsNull([date1] And [date2] And [date3])

 

[Guest]

Hi Bob - what error are you getting? How did you create your DATE boxes on
the form, if they are Text Boxes you can't use MAX. You may need to create a
Table with just the date fields and bind the form to it. What happens to the
Date boxes afterwards?
Yours - Dika

 

[Guest]

Call the following function in the After Update event of your 3 date
controls. If any of the date controls are not filled in, it will return
Null; otherwise, it will return the maximum of the 3 dates.

Function MaxDate(varDate1 As Variant, varDate2 As Variant, varDate3 As
Variant) As Variant
Dim varHighDate As Variant
If IsNull(varDate1) Or IsNull(varDate2) Or IsNull(varDate3) Then
MaxDate = Null
Exit Function
End If
If varDate1 > varDate2 Then
varHighDate = varDate1
Else
varHighDate = varDate2
End If
If varDate3 > varHighDate Then
varHighDate = varDate3
End If
MaxDate = varHighDate
End Function