How do I insert a LF in a default value in an ntext field in SQL table?

did you try
chr(10) -- for LF
chr(13) -- for CR

You may not be able to do his easily...
AFAIK, SQL Server does not support fields with CR/LF characters, and have them be interpreted as true Carriage Returns/Line Feeds.

You can do this in Access 2007 and higher if you use a "Rich Text" field.
But you need to create the Default value with "HTML" line feeds (Rich Text is really HTML)
So your default value might look something like this:
"Your First Paragraph <br> Your Second Paragraph <br> Your Third Paragraph"

Perhaps there is a workaround for this in SQL, ...so lets see if another Experts can contribute more...

JeffCoachman

Concatenate CHAR(10) wherever you need a line feed:
...
DEFAULT 'Paragraph 1 blahblahblah' + CHAR(10) + 'Paragraph 2 moreblah' + CHAR(10) + 'Paragraph 3 endofblah'
...

For example:
declare @text nvarchar(4000)
set @text = 'Paragraph 1 blahblahblah' + CHAR(10) + 'Paragraph 2 moreblah' + CHAR(10) + 'Paragraph 3 endofblah'
print @text

This looks like vb code.  I am talking about going to the field in the table from SQL Server Management Studio and entering the data.  But thanks for the response!

You will have to try both suggestions.

Note that while you can "insert" these Carriage returns/Line feeds/Breaks, "characters" ...in the field, ...they may not display as actual line breaks when you type into them...
You may need to display these fields in a specially formatted control...
Keep us posted

We've tried both suggestions with no luck and are still looking for a solution.  In the past we were able to insert line feeds and carriage returns when we used an Access project (.ADP) to make changes to the default values in the table. Now it seems that Access projects with SQL 2008 R2 will not allow design updates and SSMS is not allowing us to do it either, so we're looking for an alternate 3rd party tool/terchnique that will allow for these type of updates to the table default values.

I've read on other forums that others have had this same issue, however there wasn't a clear solution.  Thinking that copy/pasting the default values from word, notepad maybe causing the issue, we tried "programmer's notepad" with Unix line endings and that didn't work either.  

Is there a good 3rd party tool instead of SSMS that we could use that might work better with LF/CR in the default text?  Is it so unusual to have paragraphs as part of the default value?  

We use the defaults to populate Access forms and generate Access Reports, some times the values are updated, but some times  they are just left as default. Our staff likes to have the defaults in there so they can they don't have to start from scratch each time they set up a new record.

Thanks in advance for any advice or help!

I'm am not sure...

Your situation is unique in that you are not storing your data in the standard way.
Typically a "break" (Carriage return or line feed) is interpreted as a new "record" (not just multiple parts of one record.)
So your situation is similar to you having one customer record, and you then using a memo field, you store their multiple "Orders" (each order separated by a cr/lf)

In a standard normalized database you would have one Customer table and one Order Table.
So One Customer record could have many Order "records".
You could then "concatenate" the many records together (with your CR/LFs) for display purposes only .

This more standard approach would also be helpful if you wanted to filter or sort for the individual Orders.
(This is impossible with your current design.)

I will admit that I don't know your data or its purpose, ...but the whole reason why this is difficult to do is that more advanced databases like SQL Server, are a lot more structured in the way they need the data to be stored.
    "In the past we were able to insert line feeds and carriage returns when we used an Access project"
Yes Access allows this, and sometimes this spoils you into thinking that it is acceptable.
;-)
(Perhaps, now that you are moving to SQL, this might be the time to normalize this structure a bit more.)

In the end, ...then only other way I can see doing this is to add the cr/lf as HTML breaks (</br>), then use a HTML viewer control (Like the webbrowser control in Access) to display the breaks.

JeffCoachman

what about making a special character pattern to be replace later for vbCrLf ,
for example when you adding the records you separate them with "#CR#" then before display or print you replace this text with vbCrLf

If you need a full cr and line feed, you should be able to add char(13) and char(10) rather than just char(10):

DEFAULT 'Paragraph 1 blahblahblah' + CHAR(13) + CHAR(10) + 'Paragraph 2 moreblah' + CHAR(13) + CHAR(10) + 'Paragraph 3 endofblah'

Your original request just asked for a line feed, which is char(10).

We decided that will break this data field into several fields, one for each paragraph of text being stored.  Just to clear up a couple of points, the database is normalized and it's always been a SQL database, it's just that we "view" the data via Access (mdb and/or accdb with linked tables) (we use it as a "front end" for users).  In the past, we were able to use an Access project (.adp file) to insert and make changes to these default values, which included having multiple paragraphs in this one memo field.  

Based on the fact that we were having a hard time finding a way to do this and because of expert comments, it seems like the best practice solution was to break up the field into multiple fields.

Thanks for the advice!

Pointed us in the right direction, but we never did find the specific answer we were looking for.