Format (1 Viewer)

[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.

 

[Pat Hartman]

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

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

 

[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]

 

[Pat Hartman]

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

You make use of the + operand and how it handles null differently from the normal concatenation operator.

=[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]

 

[Pat Hartman]

Thanks @Josef P. for fixing my answer. I was playing bridge and shouldn't have been answering questions while I was the "dummy".

 

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

 

[Pat Hartman]

Stand back and look at the examples. Notice that there are parentheses that encase parts of the sample expressions. Within the parentheses the use of the + gives you "nothing" when any of the components are null or "something" when all parts are not null.

Something + null = null
Something & null = something

 

[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.