Remove embedded carriage control from memo fields

[dave]

Running an Access 2000 db with embeded CR/LF control codes
embedded within the textof a Memo field.

What's the most direct route to remove them , other than
manually, line-by-line?

Thanks in advance.

 

[Allen Browne]

Try using Replace() in an Update query.

1. Create a query into this table.

2. Drag the memo field into the grid. Named "MyMemo" in this example.

3. Change it to an Update query: Update on Query menu.
Access adds an Update row to the grid:

4. In the Update row, enter:
Replace([MyMemo], Chr(13) & Chr(10), " ")

5. Run the query.

 

[dave]

Allen..

Unfortunately, Access retricts the functions that can be
used in an Update query, and Replace and Translate are two
of them that are not allowed.

Thanks, though. That's what I tried first and found out
the hard way.

-----Original Message-----
Try using Replace() in an Update query.

1. Create a query into this table.

2. Drag the memo field into the grid. Named "MyMemo" in this example.

3. Change it to an Update query: Update on Query menu.
Access adds an Update row to the grid:

4. In the Update row, enter:
Replace([MyMemo], Chr(13) & Chr(10), " ")

5. Run the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Running an Access 2000 db with embeded CR/LF control codes
embedded within the textof a Memo field.

What's the most direct route to remove them , other than
manually, line-by-line?

Thanks in advance.

.

 

[Allen Browne]

Dave, I hear people say that, but just tried it in A2000 SP3.
Worked here.

Perhaps it's because I also have A2003 on the same machine, or perhaps it
was fixed in one of the service packs.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen..

Unfortunately, Access retricts the functions that can be
used in an Update query, and Replace and Translate are two
of them that are not allowed.

Thanks, though. That's what I tried first and found out
the hard way.

-----Original Message-----
Try using Replace() in an Update query.

1. Create a query into this table.

2. Drag the memo field into the grid. Named "MyMemo" in this example.

3. Change it to an Update query: Update on Query menu.
Access adds an Update row to the grid:

4. In the Update row, enter:
Replace([MyMemo], Chr(13) & Chr(10), " ")

5. Run the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Running an Access 2000 db with embeded CR/LF control codes
embedded within the textof a Memo field.

What's the most direct route to remove them , other than
manually, line-by-line?

Thanks in advance.

 

[Guest]

Allen..

Thanks...I'll check our SP version and go to 3 if need be.

Thanks again.

-----Original Message-----
Dave, I hear people say that, but just tried it in A2000 SP3.
Worked here.

Perhaps it's because I also have A2003 on the same machine, or perhaps it
was fixed in one of the service packs.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen..

Unfortunately, Access retricts the functions that can be
used in an Update query, and Replace and Translate are two
of them that are not allowed.

Thanks, though. That's what I tried first and found out
the hard way.

-----Original Message-----
Try using Replace() in an Update query.

1. Create a query into this table.

2. Drag the memo field into the grid. Named "MyMemo" in this example.

3. Change it to an Update query: Update on Query menu.
Access adds an Update row to the grid:

4. In the Update row, enter:
Replace([MyMemo], Chr(13) & Chr(10), " ")

5. Run the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Running an Access 2000 db with embeded CR/LF control codes
embedded within the textof a Memo field.

What's the most direct route to remove them , other than
manually, line-by-line?

Thanks in advance.

.

 

[Tim Ferguson]

Thanks...I'll check our SP version and go to 3 if need be.

If needed, you can wrap the functions in a VBA function and call that:

public function ChangeIt(something as variant) as variant
if isnull(something) then
changeit = null

else
changeit = replace(something,"this","that")

endif

end function


update mytable set myfield = changeit(myfield)

but you have to call it using the UI (either run the querydef or use
DoCmd.RunQuery) rather than using straight SQL/ Jet.

HTH


Tim F