Expression to remove carriage return in SSRS

Question

I am struggling to get an expression to work in SSRS.

I have an address block stored in an Oracle database with carriage return characters.

An example is the data stored in the database is:

123 Anywhere Street<CR><LF>
SOMETOWN-ON-SEA<CR><LF>
NARNIA<CR><LF>
AA1 1AA<CR><LF>

I have created an expression on the field in SSRS that looks like this:

=Replace(Fields.Address, vbCRLF, " ")

Seems like the right thing to do, but what I end up with is:

123 Anywhere StreetSOMETOWN-ON-SEANARNIAAA1 1AA

What did it do with the spaces I told it to add? Why are they not there?

Note: OK so yeah, I could just do this replace thing in PL/SQL and yeah that will solve my problem, but it's not really the point. I want to know why it isn't working in SSRS

Answer 1

The carriage return is usually represented by characters 10 and 13. This works for me:

=Replace(Replace(Fields!ADDRESS.Value, Chr(13), ""), Chr(10), " ")