Format

[kitty77]

I'm combining a few fields and need to have a return (next line) after each.

=[Street Address] & [Street Address Line 2] & [City]

Street Address
Street Address Line 2
City

 

[ebs17]

& vbCrLf

 

[kitty77]

& vbCrLf

I tried that but it keeps putting square brackets around the vbCrLf?

=[Street Address] & [vbCrLf] & [Street Address Line 2] & [City]

 

[tvanstiphout]

Where is this? An expression for the ControlSource of some control on a form or report?

 

[kitty77]

Where is this? An expression for the ControlSource of some control on a form or report?

Yes, on a textbox on a form.

 

[ebs17]

You need two line breaks according to your wishes. I mentioned the VBA constant, alternatively you can also use Chr(13) & Chr(10).
I trust everyone to correctly install it in a character string, so I won't make any further comments.

 

[tvanstiphout]

vbCrLf is a VBA constant. The Expression Service does not know about those. Replace with:
=[City] & Chr(13) & Chr(10) & [Zip]
The expression service *and* VBA know about the Chr function.

 

[kitty77]

vbCrLf is a VBA constant. The Expression Service does not know about those. Replace with:
=[City] & Chr(13) & Chr(10) & [Zip]
The expression service *and* VBA know about the Chr function.

That did it!

 

[kitty77]

So, here is my code... How can I make it remove the extra line if [Street Address Line 2] is empty?

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

 

[kitty77]

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

I get the same results as before?

 

[Josef P.]

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

 

[kitty77]

Sometimes, =[Street Address] can be null and [Street Address Line 2] have a value. So, how can I check for either null and remove that blank line?

 

[Josef P.]

=([Street Address] + (Chr(13) + Chr(10)) & ([Street Address Line 2] + Chr(13) + Chr(10)) & [City] & ", " & [State] & " " & [Zip Code]
Then both address lines can be Null.

 

[kitty77]

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

 

[Josef P.]

Use the same concept for [City] and [State]?

Principle:
Null & "abc" => "abc"
Null + "abc" => Null
(Null + "abc") & "xyz" => Null & "xyz" => "xyz"

 

[kitty77]

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

Where do I change it so the ", " and the " " don't show? Before the those or before the city, state, zip? Not clear. Sry.

 

[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?"