Re: [MS_AccessPros] Re: How can I replace CRLF (with space) in thousands of Memo and Text fields in many tables in a database?

 

Mark-

You could try:

UPDATE [EXAM] SET [EXAM].[Memofield] = CStr(Replace([EXAM].[Memofield], Chr(13) & Chr(10),'  '));

I assume this is a Memo data type, which can be tricky.  (I removed the quotes around the Chr expression.)

If that doesn't work, you'll have to do it in VBA with a Recordset.

Dim db As DAO.Database, rst As DAO.Recordset

    Set db = CurrentDb

    Set rst = db.OpenRecordset("SELECT MemoField FROM EXAM")

    Do Until rst.EOF

        rst.Edit

        rst!MemoField = Replace(rst!MemoField, vbCRLF, " ")

        rst.Update

        rst.MoveNext

    Loop

    rst.Close

    Set rst = Nothing

    Set db = Nothing

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Jul 1, 2014, at 4:56 PM, tcellr777 <no_reply@yahoogroups.com> wrote:

I've tried:

UPDATE [EXAM] SET [EXAM].[Memofield] = Replace([EXAM].[Memofield],'Chr(13) & Chr(10)','  ');

It says it worked except for 65 type conversion errors, but nothing seems changed and 12,000 fields seem to not have been changed.  Thanks - Mark

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___