How to concat SQL Server 2005 data with line feed

45 pts.
Tags:
Concatenate
Line Feed
Microsoft Access 2003
SQL 2005
SQL Server 2005
T-SQL
I am attempting to update a column in SQL Server 2005 by concatenating data from two other columns and include a line feed between the two and have the second line begin with a /.

The data will be viewed through an MS Access 2003 database with a linked table using an ODBC connection. The script I am using in MS SQL Server Management Studio Query window is:

update tblinventory set abstract = [title_1]+char(10)+char(13)+'/'+[Title_2] where ITEM_NO = 10850

However, when the results are viewed using Access I see in the abstract column data from title_one “two boxes” a / and data from title_two.

I would expect to see the /and data from title_two on a different line.

 An example is data in title_1 = Houston data in title_2 = Texas; I would expect to see data returned in abstract as Houston new line /Texas However when viewed through the linked table in Access I see in column abstract Houston BOX BOX  [two][/two]/Texas all on the same line the two boxes I assume are the special characters for line feed and carrige return but Access is not interpreting them correctly. When the same row of data is viewed through MS SQL Server Management Studio Query window the data for abstract is displayed on one line as: Houston  /Texas.

Can anyone assist with this issue? Thanks in advance



Software/Hardware used:
SQL Server 2005, Access 2003

Answer Wiki

Thanks. We'll let you know when a new response is added.

Try:

<pre> + char(13) + char(10) +</pre>

If I remember well chr(13) = CR, and chr(10) = LF, and you should add CRLF (not LFCR).

-CarlosDL

——————–

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TLMercer
    Thank you that seemed to fix the issue. Should have thought of the order myself.
    45 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following