Showing the max of three fields in a single record

[RD]

Acces Table has
Id
Date1
Date2
Date3 columns

Select date1, date2, date3 from Table where Id > 1 But now I also want to
have a column shown in the query that will show the greatest of the three
date values for each selected rown shown as GreatestDate.

Any idea on what the statement should look like, any help apperciated.

Bob

 

[Allen Browne]

You can use an IIf() inside a IIf(), or use this function that works with
any number for fields, e.g.:
Largest([Date1], [Date2], [Date3])

Function Largest(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMax As Variant 'Largest value found so far.

varMax = Null 'Initialize to null

For i = LBound(varValues) To UBound(varValues)
If IsNumeric(varValues(i)) Then
If varMax >= varValues(i) Then
'do nothing
Else
varMax = varValues(i)
End If
End If
Next

Largest = varMax
End Function

 

[Van T. Dinh]

Create a Calculated Field / Column using nested IIf in your Query:

GD: IIF([Date1] >= [Date2], IIF([Date1] >= [Date3], [Date1], [Date3]),
IIF([Date2] >= [Date3], [Date2], [Date3]) )

(typed as one line in the "Fields" row of an empty Column in your Query
grid)

The above should work if you have valid date values for all 3 Fields. If
you have Null value for any of the date Fields, it can get more complex and
you are probably better off writing a UDF (User-Defined Function) and use
the UDF in the Calculated Field / Column of your Query.

OTOH, the needs to perform "aggregate" calculations *across* a Record / rows
in database / queries are often indications that the database is incorrectly
structured. Have you checked your Table Structure against the Relational
Database Design Principles / Database Normal Forms?

 

[RD]

Thanks for the structure comments, I'm living with a legacy Unix database
structured using the Old Business basic that we connect to via an ODBC
driver, so indeed it is far from relational and creates all sorts of
problems, but the cleint has not uet seen fit to have me recreate a new
Windows UI and transfer the data to a corrcetly structured relational
database like sql server. So got to to with what I got

Again thanks guys.

Create a Calculated Field / Column using nested IIf in your Query:

GD: IIF([Date1] >= [Date2], IIF([Date1] >= [Date3], [Date1], [Date3]),
IIF([Date2] >= [Date3], [Date2], [Date3]) )

(typed as one line in the "Fields" row of an empty Column in your Query
grid)

The above should work if you have valid date values for all 3 Fields. If
you have Null value for any of the date Fields, it can get more complex and
you are probably better off writing a UDF (User-Defined Function) and use
the UDF in the Calculated Field / Column of your Query.

OTOH, the needs to perform "aggregate" calculations *across* a Record / rows
in database / queries are often indications that the database is incorrectly
structured. Have you checked your Table Structure against the Relational
Database Design Principles / Database Normal Forms?

--
HTH
Van T. Dinh
MVP (Access)

Acces Table has
Id
Date1
Date2
Date3 columns

Select date1, date2, date3 from Table where Id > 1 But now I also want to
have a column shown in the query that will show the greatest of the three
date values for each selected rown shown as GreatestDate.

Any idea on what the statement should look like, any help apperciated.

Bob