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 :)

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.
Max() does work in Access if your data is properly normalized. When your data is "flat" like a spreadsheet, you are better off using Excel. It will be far less frustrating for you. NO relational database supports aggregate functions (or others for that matter) that work "across" a row. Aggregate functions in relational database only work "down" a column, i.e. they search a single column of a recordset. If you want to search all the columns of a row, you'll need to write your own custom function.

If you want to know how to normalize your table, post back with some details of its contents and we'll help.

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

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

That's a good solution Paul but it doesn't include the code that fills the array so the problem is a little more complex.

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?

I've never passed an array to a function. I thought you needed to get ([col1],[col2],[col3],[col4],[col5]) into an array first and then pass the array. Being able to pass n arguments and have them treated as an array by the function simplifies the process.