Greater than expression not working

[mpreston14]

Hello,



I am trying to compare two numbers that are in two different columns in a query to decide which is the largest of the two. The two columns pull from separate tables.

Column 1: AR1 Points: IIf(IsNull([High AR Points]),0,[High AR Points])

Column 2: AR2 Points: IIf(IsNull([MaxOfPoints]),0,[MaxofPoints])

The third column is where I want it to select which is higher.
Column 3: Which AR is Higher?: IIf([High AR Points]>[MaxOfPoints],"AR1","AR2")

The issue I am having is that when the "IsNull" kicks in and puts a zero (0) in column 2, column 3 always selects "AR2" even if column 1 has a number.

Now it also always selects "AR2" when there is a tie, but I am less troubled with that than the above.

Your guidance is appreciated!

Mike

[ItsMe]

You may have to employ a subquery and retrieve the data for your two IIf alias' before you can aggregate your >

[CJ_London]

you've used isnull for the first two columns, but not the third. try

Column 3: Which AR is Higher?: IIf([AR1 Points]>[AR2 Points],"AR1","AR2")

also consider using the nz function for faster operation

Column 1: AR1 Points: nz([High AR Points],0)

Column 2: AR2 Points: nz([MaxOfPoints],0)

The third column is where I want it to select which is higher.
Column 3: Which AR is Higher?: IIf([High AR Points]>[MaxOfPoints],"AR1","AR2")

[mpreston14]

Thanks, I tried IsNull on the third column it sort of worked, but it didn't fix when AR2 was legitimately smaller than AR1:

ID	AR1 Points	AR2 Points	Which AR is Higher?
1	7.5	0	AR1
2	25	17.5	AR2
3	0	5.5	AR2
4	0	5	AR2
5	0	0	AR2
6	12.5	37.5	AR2
8	12.5	5.5	AR2
10	12.5	12.5	AR2
11	5	3.75	AR2
12	0	0	AR2

Here is the SQL:

SELECT Referee_T.ID, IIf(IsNull([High AR Points]),0,[High AR Points]) AS [AR1 Points], IIf(IsNull([MaxOfPoints]),0,[MaxofPoints]) AS [AR2 Points], IIf(IsNull([High AR Points])>IsNull([MaxOfPoints]),"AR1","AR2") AS [Which AR is Higher?]
FROM ([Temp 3 Highest Point Match as AR1 DETAIL] RIGHT JOIN ([Temp 3 Highest Point Match as AR2 DETAIL] RIGHT JOIN Referee_T ON [Temp 3 Highest Point Match as AR2 DETAIL].ID = Referee_T.ID) ON [Temp 3 Highest Point Match as AR1 DETAIL].RefID = Referee_T.ID) INNER JOIN Officiated_Matches_T ON Referee_T.ID = Officiated_Matches_T.RefID
GROUP BY Referee_T.ID, IIf(IsNull([High AR Points]),0,[High AR Points]), IIf(IsNull([MaxOfPoints]),0,[MaxofPoints]), IIf(IsNull([High AR Points])>IsNull([MaxOfPoints]),"AR1","AR2");

[CJ_London]

this

IIf(IsNull([High AR Points])>IsNull([MaxOfPoints]),"AR1","AR2")

is not what I posted which is

IIf([AR1 Points]>[AR2 Points],"AR1","AR2")

[mpreston14]

[AR1 Points] and [AR2 Points] return selection boxes when I run the query.

[mpreston14]

Well, I figured I did something wrong when the boxes popped up, but looking at it now, it does show the correct choice! Thank you!

[mpreston14]

Now one follow up question for the ties. If I wanted it to pick the AR1 over the AR2 if there was a tie, I assume a nested IIf would be in order?

[mpreston14]

This worked: test2: IIf([AR1 Points]>[AR2 Points],"AR1",IIf([AR2 Points]>[AR1 Points],"AR2",IIf([AR1 Points]=[AR2 Points],"AR1")))

[CJ_London]

If I wanted it to pick the AR1 over the AR2 if there was a tie

use

IIf([AR1 Points]>=[AR2 Points],"AR1","AR2")