Replace Carriage Return ASCII Character in Memo Field

[Steve]

I am trying to replace the ASCII square character for a carriage return in
several hundred rows. I would like to replace it with an actual carriage
return so the text will appear as a new line/paragraph. I put together this
SQL statement:

UPDATE MyTable SET MyField = Replace(MyField,Chr(13) & Chr(10),Chr(13))
WHERE ((InStr(MyField,Chr(13) & Chr(10))>0))

This is not making the changes that I am looking for. What is the correct
syntax to get my memo field to update?

 

[Douglas J. Steele]

You need to ensure that you DO have Chr(13) & Chr(10), otherwise you'll get
the square character.

Where did your data come from? If it's from Excel, it's likely Chr(10) only,
not Chr(13) only.

Your SQL would be

UPDATE MyTable SET MyField = Replace(MyField, Chr(10), Chr(13) & Chr(10))
WHERE ((InStr(MyField,Chr(10))>0))

 

[Ken Sheridan]

If its not a Chr(10) then the 'square character' could be a character used in
some other file format for a carriage return /line feed if the data has been
imported from elsewhere. I recall having to do this years ago with data from
dBASE memo fields, where I think the character in question was some high
ASCII character, though I don't recall which.

The first step was to identify the character in question by copying it to
the clipboard and then pasting it in as the argument for the Asc function in
the debug window. Once the character's code was known it could then be
identified by means of the Chr function and replaced with a carriage
return/line feed, Chr(13) & Chr(10). At the time I had to write a function
to do this, but the Replace function makes that unnecessary these days of
course.

Ken Sheridan
Stafford, England

 

[fredg]

I am trying to replace the ASCII square character for a carriage return in
several hundred rows. I would like to replace it with an actual carriage
return so the text will appear as a new line/paragraph. I put together this
SQL statement:

UPDATE MyTable SET MyField = Replace(MyField,Chr(13) & Chr(10),Chr(13))
WHERE ((InStr(MyField,Chr(13) & Chr(10))>0))

This is not making the changes that I am looking for. What is the correct
syntax to get my memo field to update?

In Access, the chr(13) & chr(10) combination (in that order) IS the
required code for carriage return and Line feed.
If you are seeing just the square it is probably because you have data
imported from Excel, for example, which just uses chr(10) for the
return and line feed.

Try this:
=Replace([MyField],chr(10),chr(13) & chr(10))

If that doesn't remove the create the return and line feed, then
you'll need to use code to find out what that ascii value is that is
represented as the square. Then use replace() to replace that
character with the chr(13) & chr(10) combination.