How do I maintain crlf in an Access text box? (1 Viewer)

[digitrac]

I'm importing data scraped from an old web based database and placing the contents into MS Access. The old database has text fields with crlf's and blank lines but when I write this data into Access it all disappears. If I write the same data to a VB message box the crlf's are maintained so I guess something to do with the Access field (I've tried plain and rich text but no difference):

rsu!comment = (xmlNode.nodeTypedValue) 'Write content to database
MsgBox (xmlNode.nodeTypedValue) 'Debug msgbox

So I went back to basics and simply wrote the following string to the database but still got the same result:

teststr = "This is Line 1" & vbcrlf & "This is Line 2"
rsu!comment = teststr
MsgBox = (teststr)

So it seems it could be an access field/text box format issue?

Has anyone got any code that successfully writes crlf's to an MS Access long text field formatted as "Rich Text" and displays correctly in a text box?

 

[theDBguy]

Hi. Welcome to AWF!

That doesn't sound like it should be a problem. Access should be able to store new line characters (vbCrLf or Chr(13) & Chr(10)) in the table.

Are you able to post a sample db showing the problem?

 

[Isaac]

Are you sure it's NOT doing what you want? I've found out a lot of the problem tends to be the way it looks onscreen in the Access table datasheet view.

Try opening the table, copy the line, and paste it into Notepad. Do you see the line breaks now?

 

[digitrac]

Hi both - Thanks for the welcome and quick replies

If I copy and paste I get a space character instead of crlf in notepad ++

I've attached the db

 

[Isaac]

Ok, then my next step would be to challenge the belief that there is a CRLF in the source data.
See if you can open the XML file in Notepad++ and turn View Symbols - all , on. See if it's actually there.

post a chunk of XML if you want

It's possible that the XML encoder being used to create the XML is using a linebreak character different than what Access wants, I am unsure, but what if you opened the XML in notepad++, let's say you confirm there IS some type of line-break character in there. identify what it is, let's say chr(10), and replace with chr(13) or vbnewline when adding to the recordset.

sometimes it's hard to predict how Office applications will behave.

• if I put this in Excel: ="one" & CHAR(13) & CHAR(10) & "two" Guess what I get? Actually just one single linebreak. But, if I put: ="one" & CHAR(13) & CHAR(10) & CHAR(13) & "two" , Do I still get one linebreak, because it requires both? No...I get two. Meaning, it likes Chr(13).....
• but the other day I did an access insert query into Sharepoint using the same result as my FIRST excel example, and what do I get? Two line breaks. it reads both 10 and 13 as full on line breaks, visible in rendering.

It might take some experimenting.

just some ideas.

 

[digitrac]

See if you can open the XML file in Notepad++ and turn View Symbols - all , on. See if it's actually there

Yes the crlf's are all present in the XML file

Also the test I have already carried out has a vbcrlf added to the string and it works in the msgbox but not the text box

Finally if I simply copy text manually from the old web database into the text box all the formatting is preserved

Don't understand why it can't handle a simple string which I know has crlfs but can't be preserved with formatting using VBA

 

[theDBguy]

Hi both - Thanks for the welcome and quick replies

If I copy and paste I get a space character instead of crlf in notepad ++

I've attached the db

Hi. I downloaded your file. Assuming you're referring to the Comment field in tblUpdates. Take a look at it now.

Since you're using a Rich Text format, I had to run Query1 to fix it. So, you should be able to do the same with your code.

Hope that helps...

 

[digitrac]

Yes - That has sorted the issue

Many thanks for your help!