Format

kitty77

Registered User.
Local time
Today, 12:57
Joined
May 27, 2019
Messages
715
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
 
Code:
& vbCrLf
I tried that but it keeps putting square brackets around the vbCrLf?

=[Street Address] & [vbCrLf] & [Street Address Line 2] & [City]
 
Where is this? An expression for the ControlSource of some control on a form or report?
 
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.
 
Last edited:
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.
 
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!
 
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]
 
=[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]
 
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?
 
=[Street Address] & (Chr(13) + Chr(10) + [Street Address Line 2]) & Chr(13) & Chr(10) & [City] & ", " & [State] & " " & [Zip Code]
 
Thanks @Josef P. for fixing my answer. I was playing bridge and shouldn't have been answering questions while I was the "dummy".
 
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?
 
=([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. ;)
 
Perfect! One last small item. How can I get rid of the comma (", ") and the extra space (" ") if both Address/s are null?
 
Use the same concept for [City] and [State]?

Principle:
Null & "abc" => "abc"
Null + "abc" => Null
(Null + "abc") & "xyz" => Null & "xyz" => "xyz"
 
Last edited:
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
 
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.
 

Users who are viewing this thread

Back
Top Bottom