Is there any way to make this formula shorter by using a maximum function/2nd highest value?

[bentley1]

Current Renew Date: IIf([TEST].[Date]>=[Renew_Date10] And [Renew_Date10] Is Not Null,[Renew_Date10],IIf([TEST].[Date]>=[Renew_Date9] And [Renew_Date9] Is Not Null,[Renew_Date9],IIf([TEST].[Date]>=[Renew_Date8] And [Renew_Date8] Is Not Null,[Renew_Date8],IIf([TEST].[Date]>=[Renew_Date7] And [Renew_Date7] Is Not Null,[Renew_Date7],IIf([TEST].[Date]>=[Renew_Date6] And [Renew_Date6] Is Not Null,[Renew_Date6],IIf([TEST].[Date]>=[Renew_Date5] And [Renew_Date5] Is Not Null,[Renew_Date5],IIf([TEST].[Date]>=[Renew_Date4] And [Renew_Date4] Is Not Null,[Renew_Date4],IIf([TEST].[Date]>=[Renew_Date3] And [Renew_Date3] Is Not Null,[Renew_Date3],IIf([TEST].[Date]>=[Renew_Date2] And [Renew_Date2] Is Not Null,[Renew_Date2],IIf([TEST].[Date]>=[Renew_Date1] And [Renew_Date1] Is Not Null,[Renew_Date1],[TEST].[Original_date]))))))))))

</SPAN>


When adding date 11 I am hitting a character limit so I was wondering if I could use some sort of maximum function?

also is there a way to get the 2nd highest value? The large() function I don't think is available in access

Thanks for the help!

 

[bentley1]

I think this might work:

create 10 variables called RenewMax1....10: for example renewmax5 would be max of renew_date1...renew_date 5

and then I would have


if renewdate1 is null, null, if (Date>renew_date10,renewmax10).... if Date > renew_date1,renewmax1)

or something similar