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?
4 11198
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: -
Select field1,field2,field3 from table1 where (field1 > field2) or (field1 > field3)
-
also try -
Select field1,field2,field3 from table1 where (field1 > field2) and (field1 > field3)
-
Hope this Helps,
JS
You use the iif function. -
SELECT iif([Field1] > [Field2], [Field1], [Field2]) AS HigherField
-
FROM Table1;
-
Thanks, All. This is what I ended up with, and it works perfect:
Expr1: IIf([Expr4]>[Expr6],[Expr4],[Expr6])
Thanks again.
Not a problem, good luck.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Gleep |
last post by:
Hey Guys,
I've got a table called Outcomes. With 3 columns and 15 rows
1st col 2nd col 3rdcol
outcome date price
There are 15 rows...
|
by: Rachel Curran |
last post by:
Please can anybody help me with the following:
I have two separate excel spreadsheets that I have imported into
access, each sheet holds the same fields. Both spreadsheets hold all
information...
|
by: Megan |
last post by:
Hi everybody-
I'm helping a friend with a music database. She has an old one and is
creating a new one. She wants to compare records and fields in the old
database with records and fields in the...
|
by: Megan |
last post by:
Hi everybody-
I have 2 tables, Hearings and Rulings. Both have primary keys called,
CaseID, that are autonumbers. I don't want both tables to have the
same autonumber. For example, if Hearings...
|
by: deancarstens |
last post by:
Hi,
I'm a novice with VB and I've written this pretty simple script, but
for the life of me, I can't get the last bit of the script to work. It
should compare the value entered by the user...
|
by: Simon |
last post by:
Dear reader,
For matching of two fields it is required that the fields has the same
content.
But now I have the situation that two text fields has not precisely the same
content but there...
|
by: Mark A |
last post by:
DB2 8.2 for Linux, FP 10 (also performs the same on DB2 8.2 for Windoes, FP
11).
Using the SAMPLE database, tables EMP and EMLOYEE.
In the followng stored procedure, 2 NULL columns (COMM) are...
|
by: HelpPlzz |
last post by:
I want to compare the min and max values between two dropdowns. This script sometimes gives correct result ...and sometimes not... Pls can u answer me whts goin wrong sometimes.
<html>
<head>
...
|
by: mcolson |
last post by:
I am trying to compare the last two values in the same column of a
table. First of all, I have a column titled Row_Index that uses an
index which starts at 1 and increments by 1. What I am trying...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |