Forcing a Carriage Return/Line Feed within data

Question

Post reply

Forcing a Carriage Return/Line Feed within data

unleashedmaniac

Mr or Mrs. 500

Points: 512
More actions
June 27, 2007 at 10:16 am

#117687

I have a report that is returning a column with serial numbers. If the serial number is new, I want to prepend a effective date ("Eff. 06/27/07") to the serial number and add a CRLF. I am prepending my effective date & serial number in SQL.
The report would look something like this...
Make Model Color Serial
GE BT104 Red 70923873
SMU BT105 Blue 70933329
SSD BT107 Black Eff. 6/27/07
70933577
SPD BW100 Blue 70226544
psydo code... select make, model, color, CASE WHEN StartDate <> Effectdate THEN 'Eff. ' + CONVERT(VARCHAR(20),Effectdate,1) + <CRLF CODE HERE???> + SerialNum ELSE SerialNum END AS SerialNum
Or, is this not possible?
Thanks,
James

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

Look into using the CHAR() function (not the datatype). If I recall correctly it's something like CHAR(13).

unleashedmaniac Mr or Mrs. 500 Points: 512 More actions · Answer 2

I did try that CHR(13)+CHR(10) but it just put it inside my data as a string like this.... And didn't actually do the carriage return & line feed.

Make	Model	Color	Serial
GE	BT104	Red	70923873
SMU	BT105	Blue	70933329
SSD	BT107	Black	Eff. 6/27/07 CHR(13) CHR(10) 70933577
SPD	BW100	Blue	70226544

psydo code... select make, model, color, CASE WHEN StartDate <> Effectdate THEN 'Eff. ' + CONVERT(VARCHAR(20),Effectdate,1) + CHR(13) + CHR(10) + SerialNum ELSE SerialNum END AS SerialNum

Gloriann Ten Centuries Points: 1192 More actions · Answer 3

You can use a VB function called, vbCrLf.

For example:

"text" & vbCrLf & "text"

scout Old Hand Points: 392 More actions · Answer 4

uhm.. If you did use CHR, then I guess that the problem is there: You should use CHAR, as below:

SELECT make, model, color, CASE WHEN StartDate <> Effectdate THEN 'Eff. ' + CONVERT(VARCHAR(20),Effectdate,1) + CHAR(13) + CHAR(10) + SerialNum ELSE SerialNum END AS SerialNum

Bye, Daniele.

unleashedmaniac Mr or Mrs. 500 Points: 512 More actions · Answer 5

unleashedmaniac

Mr or Mrs. 500

Points: 512

July 6, 2007 at 2:51 pm

#717805

Thanks Scout... This was the code that I needed.

Maxer SSCrazy Eights Points: 8964 More actions · Answer 6

Wouldn't this solution be better than placing your formatting in the SQL statement?

This way reporting services handles it when it displays it, however, if you format that in the SQL statement the the results themselves would include a line return.

If you try to import that into another program down the road it might cause "weird things" to happen.

Glen Harland SSC Enthusiast Points: 153 More actions · Answer 7

What about using environment.newline() in the field expression?

Something like: iif(<condition for line 1>, <line 1> + environment.newline()) + <line 2>