Date Functions in queries 1

[EmanURL]

How does one check for the most current date of three date fields in a table

Romans 8:28 All things work together for the good

 

[SkipVought]

hi,

Depends on what you mean by most current.

Could mean the MAX of your date field, assuming that the values are not future values, or the MIN of your date field assuming that the values contain future values. Or something else even.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

you can normalize the data

qryDates:

SELECT
tblDates.ID,
tblDates.field1 as dtmDate
FROM tblDates
UNION
SELECT
tblDates.ID,
tblDates.field2 as dtmDate
FROM tblDates
UNION SELECT
tblDates.ID,
tblDates.field3 as dtmDate
FROM tblDates;

then use an aggregate query

SELECT
qryDates.ID,
Max(qryDates.dtmDate) AS MaxOfdtmDate
FROM
qryDates
GROUP BY
qryDates.ID;

 

[MajP]

you can also use a function like this

Public Function maxDate(ParamArray dtmDates() As Variant) As Variant
Dim dtmDate As Variant
For Each dtmDate In dtmDates
If (IsDate(dtmDate) Or IsNull(dtmDate)) And dtmDate > maxDate Then maxDate = dtmDate
Next dtmDate
End Function

then in a query


SELECT tblDates.ID, tblDates.field1, tblDates.field2, tblDates.field3, maxDate([field1],[field2],[field3]) AS maxDate
FROM tblDates;


And as Skip pointed out this assumes that the max date is what you are asking.