query not working with empty fields on access

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

It works fine if fields are not empty, but whe开发者_JS百科n 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

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