SQL Syntax for combining fields
Posted on 2014-09-14
I have a table with multiple fields that I am trying to combine those fields into a single field. Having some issues with the proper syntax. Others have helped me to a certain point but when I then try to modify it does not work for me.
Looking for proper syntax to be able to do the following:
Am trying to combine these four fields together with proper punctuation and spacing. If ANY of the fields are blank I would like to leave out the comma or space, etc...
I thought I had that portion of it working but then found that if the State field or ZipCode were missing it would not display correctly.
Looking to do the same thing with the FirstName MiddleName and LastName fields...combining into one field with proper punctuation and spacing, and that if ANY of the fields are blank that it will automatically adjust accordingly.
Hoping someone can provide me with the proper suggestion.
Here is the latest syntax that I have tried:
SELECT Trim([BusinessStreet] & IIf(Nz([BusinessStreet]) = "", " ",", ") & [BusinessCity] & IIf(Nz([BusinessCity]) = "", " ",", ") & [BusinessState] & IIf(Nz([BusinessState]) = "", " ",", ") & " " & [BusinessPostalCode] & IIf(Nz([BusinessPostalCode]) = "", " ",", ")) AS FullName, * INTO sample7f
What am I missing???