-----Original Message-----
You might try pasting the following function into a module:
Public Function Greatest(ByVal vValue As Variant, _
ParamArray avOtherValues() As Variant) As Variant
Dim vOtherValue As Variant
Greatest = vValue
For Each vOtherValue In avOtherValues
If IsNull(vOtherValue) Then
ElseIf IsNull(Greatest) Then
Greatest = vOtherValue
ElseIf vOtherValue > Greatest Then
Greatest = vOtherValue
End If
Next vOtherValue
End Function
and then using this function in a query whose SQL looks something like this:
SELECT
[#],
Greatest([Date1], [Date2], [Date3], [Date4], [Date5]) AS [Most Recent Date]
FROM
[Your Table]
In the long run, you might want to normalize the design of your table to
something like:
#, Date Number, Date Value
1, 1, 01/01/2000
1, 2, 12/20/2001
1, 3, 12/29/2001
1, 4, 03/15/2002
1, 5, 04/01/2003
2, 1, 12/15/2002
2, 2, 01/20/2003
2, 3, 01/25/2003
2, 4, 06/15/2003
3, 1, 09/05/1999
4, 1, 03/22/2001
4, 2, 04/25/2001
4, 3, 05/18/2002
..
..
..
With this approach, you don't have to (and probably shouldn't store) Null
dates, like Date2, Date3, Date4, and Date5 for #3.
You could also answer you original question using the built-in Max function
using a query whose SQL looks something like this:
SELECT
[#],
Max([Date Value]) AS [Most Recent Date]
FROM
[Your Table]
GROUP BY
[#]
Rocco said:
I have a table set up as follow:
# Date1 Date2 Date3 Date 4 Date5
1 01/01/2000 12/20/2001 12/29/2001 03/15/2002 04/01/2003
2 12/15/2002 01/20/2003 01/25/2003 06/15/2003
3 09/05/1999
4 03/22/2001 04/25/2001 05/18/2002
5 08/07/2002 05/19/2003 06/15/2003 06/16/2003 07/31/2003
For each record, I need to know the most recent date.
The result would be:
# Result
1 04/01/2003
2 06/15/2003
3 09/05/1999
4 05/18/2003
5 07/31/2003
.
