IIF Statement problem

[ascaife]

Can anyone tell me what I'm doing wrong with this statement:

Location: (IIf([ResidentialAddressCountry]=("") Or IsNull([ResidentialAddressCountry]),"A" & [HomePOCode],"X" & [ResidentialAddressCountry]))

It works if ResidentialAddressCountry is empty, but the else section doesn't work - just gives me #Error

 

[MSAccessRookie]

Can anyone tell me what I'm doing wrong with this statement:

Location: (IIf([ResidentialAddressCountry]=("") Or IsNull([ResidentialAddressCountry]),"A" & [HomePOCode],"X" & [ResidentialAddressCountry]))

It works if ResidentialAddressCountry is empty, but the else section doesn't work - just gives me #Error

I believe that you have an extra set of () in the formula. Try the following instead:

(IIf([ResidentialAddressCountry]="" Or IsNull([ResidentialAddressCountry]),"A" & [HomePOCode],"X" & [ResidentialAddressCountry]))
​

(Note that the () has been removed)

 

[khawar]

Or more simply

IIf(nz([ResidentialAddressCountry],"")="","A" & [HomePOCode],"X" & [ResidentialAddressCountry])

 

[ascaife]

Thanks guys. No luck on the first one but option 2 worked a treat - good to learn a new one there with nz too.

Much appreciated.

Cheers,

Andrew