Compare different fields for maximum value

[TIonLI]

I'm trying to add a field in a query that will return the higher value of two other fields in a record. In Excel I can use the MAX function, and it works like a charm, but no luck in Access. In Help files it looks like the expression should be:

=MAX([DATE1],[DATE2])

Example:
If [DATE1] = 1/1/2008 12:40:00 PM
And [DATE2] = 1/1/2008 11:15:00 AM
[LATEST DATE] = 1/1/2008 12:40:00 PM

Doesn't have to be for a date, I'd be just as happy to get it working with two numbers. Any ideas?

[MindBender77]

I'm trying to add a field in a query that will return the higher value of two other fields in a record. In Excel I can use the MAX function, and it works like a charm, but no luck in Access. In Help files it looks like the expression should be:

=MAX([DATE1],[DATE2])

Example:
If [DATE1] = 1/1/2008 12:40:00 PM
And [DATE2] = 1/1/2008 11:15:00 AM
[LATEST DATE] = 1/1/2008 12:40:00 PM

Doesn't have to be for a date, I'd be just as happy to get it working with two numbers. Any ideas?

You could try this SQL in query design view:
[CODE=sql]
Select field1,field2,f ield3 from table1 where (field1 > field2) or (field1 > field3)
[/CODE]
also try
[CODE=sql]
Select field1,field2,f ield3 from table1 where (field1 > field2) and (field1 > field3)
[/CODE]

Hope this Helps,
JS

[Rabbit]

You use the iif function.

[Code=sql]
SELECT iif([Field1] > [Field2], [Field1], [Field2]) AS HigherField
FROM Table1;
[/Code]

[TIonLI]

Thanks, All. This is what I ended up with, and it works perfect:

Expr1: IIf([Expr4]>[Expr6],[Expr4],[Expr6])

Thanks again.

[Rabbit]

Not a problem, good luck.