query not working with empty fields on access

Question

What is wrong in this query running on MS Access 2003 database.

It works fine if fields are not empty, but when any of the fields is empty it results empty string.

SELECT CustomerName,IIF(Address1="","",Address1)+IIF(Address1&
Address2="","",",")+IIF(Address2="","",Address2)+IIF(Address1&Address2&
Address3="","",",")+IIF(Address3=" "," ",Address3) AS Address FROM CustomerDetails

Answer 1

You can replace ="" with is null, but the output still probably is not what you want, but you really never said what you want the output to look like.

SELECT CustomerName,IIF(Address1 is null,"",Address1)+IIF(Address1& Address2 is null,"",",")+IIF(Address2 is null,"",Address2)+IIF(Address1&Address2& Address3 is null,"",",")+IIF(Address3 is null," ",Address3) AS Address FROM CustomerDetails