query with dates

[Letica via AccessMonster.com]

I've a table with 3 dates (date1, date2 and date3). Some dates are null. I
wanted to create a query to extract the latest date from every record.
For example :
Date1 Date2 Date3 latestdate
5 Jan 09 6 Jan 09 7 Jan 09 7 Jan 09
8 Jan 09 5 Jan 09 6 Jan 09 8 Jan 09
4 Jan 09 6 Jan 09 5 Jan 09 6 Jan 09
3 Jan 09 2 Jan 09 3 Jan 09
1 Jan 09 4 Jan 09 4 Jan 09
3 Jan 09 5 Jan 09 5 Jan 09
6 Jan 09 6 Jan 09
7 Jan 09 7 Jan 09
8 Jan 09 8 Jan 09

' The latest date is null if all 3 dates are null

Could anyone help to advise how to create such query?
Thanks in advance

 

[John W. Vinson]

I've a table with 3 dates (date1, date2 and date3). Some dates are null.

Then I'd say you have an incorrectly designed table. If you have a one
(something) to many (dates) relationship, a better design would be to have two
tables in a one to many relationship, with one date per record. A Totals query
will do this very easily.

I wanted to create a query to extract the latest date from every record.
For example :
Date1 Date2 Date3 latestdate
5 Jan 09 6 Jan 09 7 Jan 09 7 Jan 09
8 Jan 09 5 Jan 09 6 Jan 09 8 Jan 09
4 Jan 09 6 Jan 09 5 Jan 09 6 Jan 09
3 Jan 09 2 Jan 09 3 Jan 09
1 Jan 09 4 Jan 09 4 Jan 09
3 Jan 09 5 Jan 09 5 Jan 09
6 Jan 09 6 Jan 09
7 Jan 09 7 Jan 09
8 Jan 09 8 Jan 09

' The latest date is null if all 3 dates are null

You'll need a rather ugly bunch of nested IIF functions, or you can adapt this
VBA routine:

Public Function GetMaximum(ParamArray vX() As Variant) As Variant
Dim iPos As Integer
GetMaximum = #1/1/100# ' earliest possible date
For iPos = 0 To UBound(vX())
If vX(iPos) > GetMaximum Then GetMaximum = vX(iPos)
Next iPos
End Function

You'ld include a calculate field in your query:

LatestDate: GetMaximum([Date1], [Date2], [Date3])

with any number of fields.

 

[Duane Hookom]

One solution would be to normalize your table with a union query like:
SELECT ID, Date1 as TheDate, 1 as DateNumber
FROM [Table with 3 dates]
UNION ALL
SELECT ID, Date2, 2
FROM [Table with 3 dates]
UNION ALL
SELECT ID, Date3, 3
FROM [Table with 3 dates];

You could save the above and then create a totals query that groups by ID
and returns the Max(TheDate).