Difficult Expression: Nesting IIfs/And/Null Values

[sbf2014]

I have three different databases from three different departments and I need to pull Date of Death information from each of them.

If Database A has a Date of Death, great. I can just map in the Date of Death from Database A.

But, if Database A is Null, Database B is Null, and Database C has a Date of Death, I need to map in the Date of Death from Database C.

If Database A is Null, Database C is Null, and Database B has a Date of Death, I need to map in the Date of Death from Database B.

If Database A, B, and C are all Null, I need the final result to show a Null value.

I've tried to write a starting expression below, but it doesn't work (which I expected). Can anyone help with this expression so that it gives me the results described above?

IIf([dodA] Is Not Null, [dodA], IIf([dodA] Is Null AND [dodB] Is Null AND [dodC] Is Not Null, [dodC], IIf([dodA] Is Null AND [dodC] Is Null AND [dodB] Is Not Null, [dodB], Null)))

 

[pr2-eugin]

Database A, B, C? Are you sure you are not referring to Fields/Tables? You can use the Nz() function to work through Null values.

 

[sbf2014]

Nevermind... the expression above actually DID work! I just had a type in the expression.

 

[sbf2014]

Sorry, I should have said 'Tables' rather than 'Databases' originally. I imported tables from three different databases into my final database to create the query.

 

[pr2-eugin]

If you are interested, (also I am sure you are referring to fields rather than tables.)

Nz([FieldA], Nz([FieldB], [FieldC]))

 

[Brianwarnock]

If the date can only appear in one DB the just code

Iif(doda is not null,doda,iif (dodb is not null, dodb,Iif(dodc is not null ,dodc,Null)))

Brian

 

[Brianwarnock]

Hmm, I like that Paul, can't say that I've ever been that clever.

Brian

 

[pr2-eugin]

Hmm, I like that Paul, can't say that I've ever been that clever.

Brian

LOL ! Come on Brian, all that I learned is through AWF. If anything the credit goes to my masters in AWF i.e people like you !