I am trying to write a proper SQL statement that would combine multiple fields into a single field and if any of those fields were blank it would still put the rest of the information in with the proper punctuation.
I have written the following statement:
SELECT Trim(+ [BusinessStreet] & (", " + [BusinessCity]) & (", " + [BusinessState]) & " " & [BusinessPostalCode]) AS FullAddress, * INTO sample7
When I then run this I do not get the results I am looking for. If there is a PO Box in the BusinessStreet field then that is all that gets displayed...everything else is left out. If there is nothing in the BusinessStreet field and BusinessStreet field then it still places a comma before the BusinessState field.
Hoping someone can tell me what I am missing and provide me with the proper syntax to accomplish.
Thank you for your time