Comparing Dates

[gargara]

Hallo , I have a query that returns the MAX Dates from 5 different colums , now i have to do a column which defines the last Date between those five colums.
Can anyone help me with some expression , which i can write in the expression builder in the new column?

 

[raskew]

Hi -

Try copying/pasting this to a standard module

Public Function MaxValueVariantArray(ParamArray ValuesArray() As Variant) As Variant
' ** THIS FUNCTION RETURNS THE MAXIMUM VALUE FROM AN ARRAY OF VALUES.
' source: http://www.accessmonster.com/Uwe/Forum.aspx/access-reports/9858/MAX-Function

Dim xlngLB As Long, xlngUB As Long, xlngCnt As Long
Dim xvarTp As Variant

xlngLB = LBound(ValuesArray)
xlngUB = UBound(ValuesArray)
If xlngUB >= 0 And xlngLB >= 0 Then
    xvarTp = ValuesArray(xlngLB)
    For xlngCnt = xlngLB + 1 To xlngUB
       If ValuesArray(xlngCnt) > xvarTp Then xvarTp = ValuesArray(xlngCnt)
    Next xlngCnt
End If

MaxValueVariantArray = xvarTp

End Function

...then structure your query along these lines, replacing table/field names as appropriate.

SELECT
    tblDateTest.Fld1
  , tblDateTest.Fld2
  , tblDateTest.Fld3
  , tblDateTest.Fld4
  , MaxValueVariantArray([tblDateTest].[fld1],[tblDateTest].[fld2],[tblDateTest].[fld3],[tblDateTest].[fld4]) AS Expr1
FROM
   tblDateTest;

HTH - Bob

 

[raskew]

Actually, provided all fields are coming from one table, your query can be shortened to:

SELECT
    Fld1
  , Fld2
  , Fld3
  , Fld4
  , MaxValueVariantArray([fld1],[fld2],[fld3],[fld4]) AS Expr1
FROM
   tblDateTest;

Bob