CODEWITHSUNDEEP
reporting-servicesconcatenationblank-line
Abby
Abby

Reputation: 93

SSRS using vbcrlf in TextBox to Concatenate field need exclude blank return line

I've created an SSRS report to print per address.

I am using vbcrlf to concatenate multiple fields in one text box.

On some occurrences, one of the fields with in the concatenate might be null. If this is the case, the line returned creates a blank line within my textbox.

Is there a way to remove the blank line break?

=Fields!SOP10200_ADDRESS1.Value & vbcrlf & Fields!SOP10200_ADDRESS2.Value & vbcrlf & TRIM(Fields!SOP10200_CITY.Value) & "," & TRIM(Fields!SOP10200_STATE.Value) & " " & TRIM(Fields!SOP10200_ZIPCODE.Value)

Expected Results:

4349 Green Ash Dr.
Earth City,MO 63045

Actual Results:

4349 Green Ash Dr.

Earth City,MO 63045

Upvotes: 1

Views: 671

Answers (2)

Abby
Abby

Reputation: 93

Thank you Hannover. I had to change it slightly and then it worked!

=Fields!SOP10200_ADDRESS1.Value & vbcrlf &
IIF(ISNOTHING(Fields!SOP10200_ADDRESS2.Value)
OR Fields!SOP10200_ADDRESS2.Value = "",vbcrlf,"")
 & TRIM(Fields!SOP10200_CITY.Value) & "," & TRIM(Fields!SOP10200_STATE.Value)
 & " " & TRIM(Fields!SOP10200_ZIPCODE.Value)

Upvotes: 0

Hannover Fist
Hannover Fist

Reputation: 10860

I would add in an IIF statement to check the Address 2 field to make sure it's not NULL or blank. If it is blank or null then have a blank otherwise out in the VBCRLF.

IIF(ISNOTHING(Fields!SOP10200_ADDRESS2.Value) OR Fields!SOP10200_ADDRESS2.Value = "", "", VBCRLF)

So your expression would be 

=Fields!SOP10200_ADDRESS1.Value & vbcrlf & 
 Fields!SOP10200_ADDRESS2.Value & 
 IIF(ISNOTHING(Fields!SOP10200_ADDRESS2.Value) 
      OR Fields!SOP10200_ADDRESS2.Value = "", "", VBCRLF) & 
 TRIM(Fields!SOP10200_CITY.Value) & "," & 
 TRIM(Fields!SOP10200_STATE.Value) & " " & 
 TRIM(Fields!SOP10200_ZIPCODE.Value)

Upvotes: 2

Related Questions