IIF Nested? (1 Viewer)

[Gunnerp245]

I have a report based on a crosstab query with columns [ABC], [BCD], [CDE], [DEF]. I want to compare the data (dates) in the four columns and display in a new column [XYZ] on the report only the latest date.

Original
......[ABC].....[BCD]....[CDE]....[DEF]
1....5/1/09...4/1/08...3/1/07...2/1/08
2...._____...2/1/08...4/1/07...12/1/09
3....1/1/07...2/1/08..._____...12/1/06
4....1/1/05...2/1/03...4/1/07...12/1/02

New
.....[XYZ]
1.....5/1/09
2....12/1/09
3....2/1/08
4....4/1/07

I can construct the basic IFF(expr, true, false), but not a nested one.

Gunner...

 

[Gunnerp245]

This seems to work;

=IIf((Nz([ABC]))>(Nz([BCD])),[ABC],IIf((Nz([BCD]))>(Nz([CDE])),[BCD],IIf((Nz([CDE]))>(Nz([DEF])),[CDE],IIf((Nz([DEF]))>(Nz([ABC])),[DEF],[ABC]))))



Gunner...

 

[pbaldy]

This would be an option:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209857

 

[Gunnerp245]

I shall give it a try.

Gunner...

 

[Gunnerp245]

Solved!

This seems to work;
=IIf((Nz([ABC]))>(Nz([BCD])),[ABC],IIf((Nz([BCD]))>(Nz([CDE])),[BCD],IIf((Nz([CDE]))>(Nz([DEF])),[CDE],IIf((Nz([DEF]))>(Nz([ABC])),[DEF],[ABC]))))

The syntax is correct, but it will not always return the correct value e.g.
In row 4., the IIF would stop after the comparison of [ABC] > [BCD], returning 1/1/05 vice 4/1/07;

Original
......[ABC].....[BCD]....[CDE]....[DEF]
4....1/1/05...2/1/03...4/1/07...12/1/02

The Maximum/Minimum function per 'pbaldy's' link
http://support.microsoft.com/default...b;en-us;209857
was JUST the ticket!

Gunner...

 

[pbaldy]

Once you understand how IIf() works, you'll see why it did that, and why it wasn't a really good option for what you were trying to do. The IIf() contains 3 parts, the test, what to do if the test is true, and what to do if the test is false. If the first test returns true (ABC > BCD in your case), it returns the true part and disregards the rest (for our purposes anyway), even though the false part may have contained further tests from your point of view. It could be done, but it would be pretty complex; that function is a lot simpler.