Format

[Pat Hartman]

=[Street Address] & (Chr(13) + Chr(10) + [Street Address Line 2]) & Chr(13) & Chr(10) & [City] & ", " & [State] & " " & [Zip Code]

=[Street Address] & (Chr(13) + Chr(10) + [Street Address Line 2]) & Chr(10) & [City] & (", " + ([State] + " " + [Zip Code]))

I nested the state and zip part because you have to handle that expression separately from the city, comma expression. You only want the space if both state and zip are not null. And you only want the comma if both city and the state/zip expression are not null

I know this is hard to get your head around. Practice. This is a very important and useful feature to understand when you are trying to properly format names and addresses.

 

[kitty77]

It gets rid of the comma and the extra space but everything is on one line now.

 

[theDBguy]

Perfect! One last small item. How can I get rid of the comma (", ") and the extra space (" ") if both Address/s are null?

Pardon me for jumping in, but I don't understand how you're getting a comma and an extra space when you're not even using them for either address. Instead, you are using the comma after [City] and the extra space after [State].

Are you saying when both Addresses are null then the city and state are also null?

 

[theDBguy]

It gets rid of the comma and the extra space but everything is on one line now.

Again, I'm still confused. If there are no addresses nor city nor state, then what else is there that it's now on "one line?"

 

[Pat Hartman]

It gets rid of the comma and the extra space but everything is on one line now.

We can't see the individual data values. We can't see the results.