How to compare fields

L

Luther

Hi,

I need to compare three numeric fields and pick the greater of the three; I
will then have to use that number in subsequent calculations.

Any help with this will be greatly appreciated.

Thanks.
 
O

Ofer

I would like to know the answer to that question also, if you get no better
answer you can use the long way

IIF(IIf(Field1>Field2,Field1,Field2)<Field3,Field3,IIf(Field1>Field2,Field2,Field1)))
 
O

Ofer

I would like to know the answer to this question also, if you'll get no
better answer, try this

IIF(IIF(Field1>Field2,Field1,Field2)<Field3,Field3,IIF(Field1>Field2,Field1,Field2)))
 
J

John Vinson

Hi,

I need to compare three numeric fields and pick the greater of the three; I
will then have to use that number in subsequent calculations.

Any help with this will be greatly appreciated.

Thanks.
Click to expand...

You may want to look at your table normalization: the need to do this
suggests that you have a one-to-many (one to three in this case)
relationship embedded within each record.

That said... a nasty nested IIF will work:

IIF([A] > , IIF([A] > [C], [A], IIF( > [C], , [C]), IIF( >
[C], , IIF( > [C], , [C]))

You'll need to check the logic and parentheses, that's just off the
top of my head...

John W. Vinson[MVP]
 
L

Luther

Sorry, this did not work; It needs to return the maximum value among the 3
fields.
Any other ideas, please?
 
L

Luther

Sure...

SELECT
Max(IIf(IIf([POSEN1]>[POSEN2],[POSEN1],[POSEN2])<[POSEN3],[POSEN3],IIf([POSEN1]>[POSEN2],[POSEN2],[POSEN1]))) AS POS_MAX
FROM HIPlanNames;

the test values were posen1=50000, posen2=20000, posen3=21562
 
O

Ofer

Try this
SELECT
Max(IIf(IIf([POSEN1]>[POSEN2],[POSEN1],[POSEN2])<[POSEN3],[POSEN3],IIf([POSEN1]>[POSEN2],[POSEN1],[POSEN2]))) AS POS_MAX
FROM HIPlanNames

In the last selection, you put the wrong selection
--
I hope that helped
Good luck


Luther said:
Sure...

SELECT
Max(IIf(IIf([POSEN1]>[POSEN2],[POSEN1],[POSEN2])<[POSEN3],[POSEN3],IIf([POSEN1]>[POSEN2],[POSEN2],[POSEN1]))) AS POS_MAX
FROM HIPlanNames;

the test values were posen1=50000, posen2=20000, posen3=21562

Ofer said:
I would like to know the answer to this question also, if you'll get no
better answer, try this

IIF(IIF(Field1>Field2,Field1,Field2)<Field3,Field3,IIF(Field1>Field2,Field1,Field2)))
Click to expand...
Click to expand...
 
L

Luther

Thank you very much. That's exactly what I need.

Ofer said:
Try this
SELECT
Max(IIf(IIf([POSEN1]>[POSEN2],[POSEN1],[POSEN2])<[POSEN3],[POSEN3],IIf([POSEN1]>[POSEN2],[POSEN1],[POSEN2]))) AS POS_MAX
FROM HIPlanNames

In the last selection, you put the wrong selection
--
I hope that helped
Good luck


Luther said:
Sure...

SELECT
Max(IIf(IIf([POSEN1]>[POSEN2],[POSEN1],[POSEN2])<[POSEN3],[POSEN3],IIf([POSEN1]>[POSEN2],[POSEN2],[POSEN1]))) AS POS_MAX
FROM HIPlanNames;

the test values were posen1=50000, posen2=20000, posen3=21562

Ofer said:
I would like to know the answer to this question also, if you'll get no
better answer, try this

IIF(IIF(Field1>Field2,Field1,Field2)<Field3,Field3,IIF(Field1>Field2,Field1,Field2)))

--
I hope that helped
Good luck


:

Hi,

I need to compare three numeric fields and pick the greater of the three; I
will then have to use that number in subsequent calculations.

Any help with this will be greatly appreciated.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer

Any time
--
I hope that helped
Good luck


Luther said:
Thank you very much. That's exactly what I need.

Ofer said:
Try this
SELECT
Max(IIf(IIf([POSEN1]>[POSEN2],[POSEN1],[POSEN2])<[POSEN3],[POSEN3],IIf([POSEN1]>[POSEN2],[POSEN1],[POSEN2]))) AS POS_MAX
FROM HIPlanNames

In the last selection, you put the wrong selection
--
I hope that helped
Good luck


Luther said:
Sure...

SELECT
Max(IIf(IIf([POSEN1]>[POSEN2],[POSEN1],[POSEN2])<[POSEN3],[POSEN3],IIf([POSEN1]>[POSEN2],[POSEN2],[POSEN1]))) AS POS_MAX
FROM HIPlanNames;

the test values were posen1=50000, posen2=20000, posen3=21562

:

I would like to know the answer to this question also, if you'll get no
better answer, try this

IIF(IIF(Field1>Field2,Field1,Field2)<Field3,Field3,IIF(Field1>Field2,Field1,Field2)))

--
I hope that helped
Good luck


:

Hi,

I need to compare three numeric fields and pick the greater of the three; I
will then have to use that number in subsequent calculations.

Any help with this will be greatly appreciated.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
L

Luther

Spoke too soon....Shouldn't this return 2 values? I added another record and
it only returned the first value.
new record: posen1 =136, posen2=10, posen3=5

Ofer said:
Any time
--
I hope that helped
Good luck


Luther said:
Thank you very much. That's exactly what I need.

Ofer said:
Try this
SELECT
Max(IIf(IIf([POSEN1]>[POSEN2],[POSEN1],[POSEN2])<[POSEN3],[POSEN3],IIf([POSEN1]>[POSEN2],[POSEN1],[POSEN2]))) AS POS_MAX
FROM HIPlanNames

In the last selection, you put the wrong selection
--
I hope that helped
Good luck


:

Sure...

SELECT
Max(IIf(IIf([POSEN1]>[POSEN2],[POSEN1],[POSEN2])<[POSEN3],[POSEN3],IIf([POSEN1]>[POSEN2],[POSEN2],[POSEN1]))) AS POS_MAX
FROM HIPlanNames;

the test values were posen1=50000, posen2=20000, posen3=21562

:

I would like to know the answer to this question also, if you'll get no
better answer, try this

IIF(IIF(Field1>Field2,Field1,Field2)<Field3,Field3,IIF(Field1>Field2,Field1,Field2)))

--
I hope that helped
Good luck


:

Hi,

I need to compare three numeric fields and pick the greater of the three; I
will then have to use that number in subsequent calculations.

Any help with this will be greatly appreciated.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

Try this untested Switch function:

SWITCH(FieldA>=FieldB and FieldA>=FieldC, FieldA, FieldB>=FieldC, FieldB, True, FieldC)

If any of the fields can be null then you'll need to use the NZ function around
them and assign a value that is smaller than the smallest expected value. Or
test for null.
 
L

Luther

Hi,

Can you please tell me what's the logic behind the comparison groups. What
I'm trying to do is to have a generic template, regardless of the number of
fields to compare. You can take my solution and explain from there.

Thanks in advance.

Ofer said:
Any time
--
I hope that helped
Good luck


Luther said:
Thank you very much. That's exactly what I need.

Ofer said:
Try this
SELECT
Max(IIf(IIf([POSEN1]>[POSEN2],[POSEN1],[POSEN2])<[POSEN3],[POSEN3],IIf([POSEN1]>[POSEN2],[POSEN1],[POSEN2]))) AS POS_MAX
FROM HIPlanNames

In the last selection, you put the wrong selection
--
I hope that helped
Good luck


:

Sure...

SELECT
Max(IIf(IIf([POSEN1]>[POSEN2],[POSEN1],[POSEN2])<[POSEN3],[POSEN3],IIf([POSEN1]>[POSEN2],[POSEN2],[POSEN1]))) AS POS_MAX
FROM HIPlanNames;

the test values were posen1=50000, posen2=20000, posen3=21562

:

I would like to know the answer to this question also, if you'll get no
better answer, try this

IIF(IIF(Field1>Field2,Field1,Field2)<Field3,Field3,IIF(Field1>Field2,Field1,Field2)))

--
I hope that helped
Good luck


:

Hi,

I need to compare three numeric fields and pick the greater of the three; I
will then have to use that number in subsequent calculations.

Any help with this will be greatly appreciated.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Top