I Need a Non-Manual Way that WORKS to Replace a Character with a Carriage Return!

[manonash]

I am using Access 2016 on Windows 11. Please see the image below for a description of the issue. Thank you!

[Minty]

Well that is a little interesting... In the immediate window:

? "A " & chr(13) & chr(10) & "B"
A 
B

So in theory that should work, those are the correct Chr() values.
Again in the immediate window

?Replace ("A@@B","@@" , chr(13) & chr(10))
A
B

I then thought it might be the double @@@@ but nope that works as well:

?Replace ("A@@@@B","@@" , chr(13) & chr(10))
A


B

So I have no idea why your's doesn't work.
In Notepad ++ the last example shows up as

?Replace ("A@@@@B","@@" , chr(13) & chr(10))
ACRLF
CRLF
BCRLF

[Micron]

IIRC, you cannot show line wraps in a textbox unless the table field is set to Long Text (aka Memo). Could that be the issue (because your query expression looks good)? Or check your table field instead of the form. You might find that the field text is wrapped but the form control isn't showing it.

[manonash]

Thanks for looking at this, Minty and Micron. The [Body] field is type Long Text, and the Text Format property on both the table and the form is set to "Rich Text". I could've left the form out of the mockup. It's irrelevant, because the problem can be seen in the table directly.

I first tried using the Import dialog to bring the data in. Then I tried it with VBA code. Then with the update query. The *only* way I've been able to get a real carriage return in there is by manual entry.

I am using Access2016. Any chance this is just an old bug that never got fixed?

[Micron]

Any chance this is just an old bug that never got fixed?

I'd say no, because it works for me:

UPDATE myTable SET myTable.Item = Replace([Item],"A",Chr(13) & Chr(10))
WHERE ((Mid([Item],2,1)="A"));

The values CA8 were replaced with C and a line wrap and the 8 character.

Item_No	Code1	Item	Country	Temp F
4	CA	C 8	AT1
1	CA	C 8	BE1
2	CA	C 8	CN1
3	CA	C 8	CNB3

The table Item field is short text. I have not tested this with a form because you say that the issue can be seen in the table. Your table row height is sufficient to show 2 or more lines I hope. Consider copying, compact/repair the copy, zip it and post here. See "How to attach files" at the top if you need help with that.

[Minty]

Does RichText recognise CrLF ?
I thought it needed to be <BR> or <P> inserted?

Try copying the table and turning off the rich text, just as an experiment?

[Micron]

I think you are on to something there. RTF is like HTML.

[manonash]

Does RichText recognise CrLF ?
I thought it needed to be <BR> or <P> inserted?

Brilliant! That was the problem: I needed to replace the text token representing the newline with "<p>" -- the HTML code for a new paragraph -- rather than Chr(13) & Chr(10)!

I did a few experiments to consolidate my understanding of the whole business. Here, for the benefit of anyone who may need it in the future, is what I found and/or concluded:

--------------------------------------------------------------------------------------------------------------------------
IMPORTING TEXT DATA WITH LINE BREAKS INTO ACCESS FROM EXCEL

I experimented with two different forms of spreadsheet with data to be imported. The first, in a "TextWithLineBreaks" column, contained text tokens ("¶¶") -- not line breaks, but rather simply the "¶" character. The second contained line breaks. The latter were created using the Replace dialog in Excel, replacing each text token ("¶¶") with the character that entering CTRL+J in the "with" textbox of the Replace dialog produces.


Here are the results I got importing these two different versions of the spreadsheet.

Source: Excel spreadsheet with line breaks in the [TextWithLineBreaks] field
Target: Access Table with a [TextWithLineBreaks] field set for PLAIN Text;
Result: The line breaks appear simply to get discarded. Each line from the text in the Body cell of a given row runs into the next line with no intervening spaces or line break.

Source: Excel spreadsheet with line breaks in the [TextWithLineBreaks] field
Target: Access Table with a [TextWithLineBreaks] field set for RICH Text;
Result: The line breaks are converted to SPACEs. (Confirmed by copying and pasting some of the text into Notepad++ and displaying it in hex view.)


Importing Text with Tokens, and Replacing the Tokens After Import
When I import the version of the Excel spreadsheet that has text tokens instead of line breaks, both versions of the receiving database table (Plain Text and Rich Text) seem to store exactly the same data.

If I then perform a Replace operation on the text tokens using an update query, to get the data to display correctly, I must replace the tokens as follows:

Receiving database table: [TextWithLineBreaks] field set for PLAIN Text
Replace token ("¶¶") with Chr(13) & Chr(10).

Receiving database table: [TextWithLineBreaks] field set for RICH Text
Replace token ("¶¶") with "<p>". (No doubt "<br>", "<p/>", and "<br/>" would also work.)


In summary, the successful process for importing formatted text with line breaks into Access from an Excel spreadsheet is as follows:

1. Replace line breaks in the Excel spreadsheet with a suitable text token of your choice. The token must, of course, be a character or string of characters that does not otherwise appear in the data.
2. Import the spreadsheet into the Access table.
3. If support for Rich Text is desired in one or more of the Long Text fields in the receiving table, set that field's "Text Format" property to "Rich Text". This can be done either before, or after, the data import, but must be done before executing the Replace operation.
4. If the target field or fields are set for Plain Text, use an update query to replace every occurrence of the text token in the data with Chr(13) & Chr(10).
5. If the target field or fields are set for Rich Text, use an update query to replace every occurrence of the text token in the data with either "<p>" or "<br>" (depending upon whether you want paragraphs or just line breaks).
6. If the data is to be displayed in a form (or presumably a report), be sure to set the Text Format property in any control that will display text with line breaks to the appropriate value. (E.g., if storing Rich Text in the table, set the Text Format property of the form control to "Rich Text").

Thanks so much, both of you, for the indispensable assistance with this!

[Micron]

Thanks for posting your testing and solution. I can't help but wonder what would happen if the table field was just short text and not formatted as rtf because the query example I showed worked with ASCII character codes and short text. TBH, I thought in order to format a field as rtf it had to be long text, but you say you are using short.

[manonash]

Thanks for posting your testing and solution. I can't help but wonder what would happen if the table field was just short text and not formatted as rtf because the query example I showed worked with ASCII character codes and short text. TBH, I thought in order to format a field as rtf it had to be long text, but you say you are using short.

No, I said I was using Long Text: and I am. As I mentioned in my description of my testing and solution, the Long Text field will display Chr(13) & Chr(10)'s as line breaks IF the Text Format is set to "Plain Text". Since a Short Text field doesn't have a Text Format property and doesn't support Rich Text, I would expect it to handle the Chr(13) & Chr(10)'s as line breaks by default, which you reported that it did.

[Micron]

Sorry, I remembered wrong. If anyone had asked me I would have said that Long Text (aka Memo) field supports rtf (e.g. <P>, <BR> and the like, but not ASCII characters like chr(13). There would be no need to support both, and the rtf/html format would not know what to do with them. In post 3 I was wrong about needing Long Text to show line wraps and tried to correct that in post 5. I hope anyone reading this in the future sees that. Again, it's great that you have followed up like you did. Many don't.