Max() function Code

[ZMAN2]

Does anyone know of a function or code that will compare several date fields in a table and return the highest or max for each record. This is very easily accomplished in excel by using the Max(Date1,Date2,Date3, etc..) function, but this will not work in Access. I get an error stating that I have used an incorrect number of arguments. Any help would be appreciated!

Thanks

 

[sfreeman@co.mer]

Not the most elegant, but you could nest IIF statements... like...

Assuming
table 'tblDates' and
3 date fields, 'Date1', 'Date2', and 'Date3'.

Query could be:

SELECT
tblDates.Date1 AS D1
, tblDates.Date2 AS D2
, tblDates.Date3 AS D3
, IIf([D1]>[D2] And [D2]>[D3],[D1],IIf([D2]>[D3] And [D1]>[D3],[D2],[D3]))

AS
Expr1

FROM
tblDates;

Need to adjust if more than 3 fields, and account for null values.
HTH

 

[pbaldy]

I agree with the need to normalize your data, but until then here's one solution:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209857

 

[pbaldy]

I'm not clear on what's missing, Pat (but then it's getting late here). I've never needed this code, but I was able to build a working sample with the code in the article. I called it with:

MaxVal: Maximum([col1],[col2],[col3],[col4],[col5])

Have I missed something in the problem, or am I just dense tonight?