MaxCertDate: IIf([Property]>=[Security] And [Property]>=[A/R Statement] And
[Property]>=[Patent Cert],[Property]
,IIf([Security]>=[Property] And [Security]>=[A/R Statement] And
[Security]>=[Patent Cert],[Security]
,IIf([A/R Statement]>=[Security] And [A/R Statement]>=[Property]
And [A/R Statement]>=[Patent Cert],[A/R Statement],#9/9/9999#)))
Paste the following function into a VBA module and save it.
Public Function MaxVal(ParamArray MyArray()) As Variant
Dim varMax As Variant
Dim intLoop As Integer
varMax = Null
For intLoop = LBound(MyArray) To UBound(MyArray)
If IsNull(MyArray(intLoop)) Then
'do nothing
ElseIf IsNull(varMax) Or MyArray(intLoop) > varMax Then
varMax = MyArray(intLoop)
End If
Next
MaxVal = varMax
End Function
Now in the query use
NZ(MaxVal([Property],[Security],[Patent Cert],[A/R Statement]),#9/9/9999#)
I can't see how having two dates the same would cause you to get the wrong
result in your expression. HOWEVER, if you have NULL dates, that could cause
you to end up with #9/9/9999# even though you had valid dates in other fields.
To handle that you might try using something like the following to handle nulls.
IIf([Property]>=Nz([Security],[Property)
And [Property]>=Nz([A/R Statement],[Property])
And [Property]>=Nz([Patent Cert],Property), [Property]
, IIf([Security]>=NZ([Property],[Security])
And [Security]>=NZ([A/R Statement],[Security])
And [Security]>=NZ([Patent Cert],[Security]),[Security],...
You might try
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I understand how to find the max of dates from multiple records, but I have
dates within one record (different fields) and I want the latest date in yet
another field. There has to be a way to do this in a query. My formula almost
works, but I forgot to account for when two fields have the same dates. I
then end up with my own "error code" of "9/9/9999" when that happens when I
should get the date. Any ideas?
Thanks,
MaxCertDate: IIf([Property]>=[Security] And [Property]>=[A/R Statement] And
[Property]>=[Patent Cert],[Property],IIf([Security]>=[Property] And
[Security]>=[A/R Statement] And [Security]>=[Patent Cert],[Security],IIf([A/R
Statement]>=[Security] And [A/R Statement]>=[Property] And [A/R
Statement]>=[Patent Cert],[A/R Statement],#9/9/9999#)))
Jerry Whittle said:
The problem is your table structure. You are going across with column heading
as in Excel.
What you need is a table something like below named something like Certs:
CertPK ProjectFK Certype CertDate
1 2 1 1/1/09
2 2 2 2/22/09
3 2 3 5/16/09
4 2 4 10/24/09
The CertPK is just an autonumber primary key field.
The ProjectFK is the foreign key linked to the Project, person, or whatever.
The CertType is what you field heading use to represent.
The CertDate is a date/time field.
With the above, you could easily use something like a Totals query or DMax
function to find the newest CertDate.
