Remove Linebreaks

[RedHeadedMonster]

Data has been imported (from Word, Excel &/or PDF) into a Memo field in an Access 2007 database. Sometimes the data appears fine. Other times there appears to be excessive and unnecessary Linebreaks / paragraph returns throughout the data in the Memo field. Is there a way to search for and replace these linebreaks?

Thanx!
RHM

 

[Beetle]

You can search for the character codes for Carriage Return - Chr(13) and Line Feed - Chr(10) and replace them. You would need to be careful because there may be instances of CR/LF that you don't want to replace and end up with just one long sting of text.

At first - and on a backup copy of your db - you might want to look for instances where there is more than one occurrence of CR/LF and replace it with one occurrence. Example;

UPDATE MyTable SET MyMemoField = Replace(MyField, (Chr(13) & Chr(10)) & (Chr(13) & Chr(10)), Chr(13) & Chr(10));

 

[RedHeadedMonster]

Thanx! especially for providing the complete Replace statement.
I'll have a Beer ;-)
Have a nice Day!