By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,098 Members | 2,007 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,098 IT Pros & Developers. It's quick & easy.

Compare different fields for maximum value

P: 11
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?
Feb 5 '08 #1
Share this Question
Share on Google+
4 Replies


MindBender77
100+
P: 234
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:
Expand|Select|Wrap|Line Numbers
  1. Select field1,field2,field3 from table1 where (field1 > field2) or (field1 > field3)
  2.  
also try
Expand|Select|Wrap|Line Numbers
  1. Select field1,field2,field3 from table1 where (field1 > field2) and (field1 > field3)
  2.  
Hope this Helps,
JS
Feb 5 '08 #2

Rabbit
Expert Mod 10K+
P: 12,366
You use the iif function.

Expand|Select|Wrap|Line Numbers
  1. SELECT iif([Field1] > [Field2], [Field1], [Field2]) AS HigherField
  2. FROM Table1;
  3.  
Feb 5 '08 #3

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

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

Thanks again.
Feb 5 '08 #4

Rabbit
Expert Mod 10K+
P: 12,366
Not a problem, good luck.
Feb 5 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.