compare 3 dates?

[mizsydney]

I am trying to compare three dates on an Access form. the later of the 3 dates is the result I need to appear in this field. I understand how to compare 2 dates, but I can't get the correct syntax to compare 3.

this is what I have in the control source for this field:

=nz(IIf(([org_date] Is Null),"",(IIf(DateValue([rep_date])>DateValue([org_date]),DateValue([rep_date]),DateValue([org_date])))))

this only compares 2 dates, and it is clumsy - I set this up over a year ago and haven't looked at it since. AND now they need 3 dates compared.

can anyone point me in the right direction? this seems simple enough, but I am very unfamiliar with Access.

TIA!

 

[MajP]

Public Function getMaxDate(ParamArray dates()) As Variant
Dim varDate As Variant
Dim tmpDate As Variant
For Each varDate In dates
If varDate > tmpDate Then
tmpDate = varDate
End If
Next varDate
getMaxDate = tmpDate
End Function

pass in ass many control values as you want

=getMaxDate([txtBxOrg,txtbxRep,.....)

 

[MajP]

should read
=getMaxDate([txtBxOrg],[txtbxRep],.....)

 

[missinglinq]

And while you don't need it for MajP's code, note that in VBA, code like

If([org_date] Is Null)

is not valid syntax. In Access VBA it would be

If(IsNull([org_date])

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[mizsydney]

thank you for the quick replies! where do I place that bit of code? I am very unfamiliar with Access, VBA etc. I should have made that clear, I apologize.

 

[MajP]

Place the code in a standard module. Then you can call it from a query, form, or report. Do not place it in a form or report's module.