Load 1 address of 3 based on latest date

[haymarketpaul]

QV11 SR4

I need to select the latest address based on the latest date where one exists eg...

Dateformat DD/MM/YY

CMS Date	CMS Address1	DSB Date	DSB Address1	MIMS Date	MIMS Address1
03/11/15	10 Richmond Road	04/05/15	126 Muirs Lane	09/02/15	555 Dunbar Street

I know i can use Date(RangeMax....   to quickly find the latest date of the 3 and

In the example above the CMS Date is the latest so i would use CMS Address1 as the latest Address1 which is fine BUT if CMS Address1 is empty i would then want to take the next available Address1 (DSB in the example above) and so on with the MIMS Address1 if the DSB one is empty.

I can satisfy this logic in a lengthy IF statement but am wondering if there's a better more efficient way to handle this?

Any advice?

[avinashelite]

Hi Paul,

Did you got the answer ?

[haymarketpaul]

Attempting to Implement at the moment will let you know

[haymarketpaul]

Several correct answers in there - thanks for everyone for the replies

[haymarketpaul]

All working nicely now - small issue if 2 dates clash as the highest

[haymarketpaul]

Couldn't get this approach to work i'm afraid but nice idea - thanks

[avinashelite]

Aha in that case ...if you have the time associated with that ? then you could decided on the time ...

otherwise you need to write a condition statement to pick either any one of the address ....like CMS or DSB address

[haymarketpaul]

Yes no time available in 2 of the 3 sources so have gone with a condition statement - thanks