Find/Replace to insert a manual return in a memo field 1

[kq]

I have a memo field that contains the character " / " to separate the text into categories. Specifically, it's book readings where each day has page readings from different sections of the book. My original intent was to create the readings and import them to Outlook which would then show up on my pda.

In my attempts, I have tried a number of ways outside of Access to have each section appear on its own line. In Excel I used the ALT+Enter scheme -- only to have it show up in Access and Outlook as a non printable character.

In the Access memo field itself, I can manually create the line breaks with CTRL+Enter and it correctly imports to Outlook with the line breaks. But I have about 300 rows with 5 categories -- too much manual intervention.

Appreciate the help!

 

[Eupher]

Hi, kq,

Have a look at the Replace() function. With the vbCrLf constant, it should probably get you going in the right direction.

Ken S.

 

[TheAceMan1]

How are ya kq . . .

In parallel with [blue]Eupher[/blue] try this in a command button ([purple]be sure to backup the table under another name so you can come back to square one![/purple]):

[blue]   Dim SQL As String, DL As String
   
   DL = vbNewLine & vbNewLine
   
   SQL = "UPDATE [purple][b]MemoTableName[/b][/purple] " & _
         "SET [[purple][b]MemoName[/b][/purple]] = Replace([[purple][b]MemoName[/b][/purple]],'/', chr(13) & chr(10) & chr(13) & chr(10));"
   DoCmd.RunSQL SQL[/blue]

Only the memo's with [blue]/[/blue] as the delimiter will be changed.

See Ya! . . . . . .

 

[kq]

Thank you both for responding ... AceMan1's solution did the trick (with the added bonus of all the code written).