Replace Carriage Return Symbol

[Apprentice]

I am trying two different lines of code to replace a common symbol that is
created in a memo field when text is cut from MS Word and pasted into the
field.

But instead of replacing the symbol, both are actually adding the symbol..
amaizing. Hope someone can help on this..

Code 1:
UPDATE ReviewBackGrnd SET Background = Replace(Background,Chr(13),Chr(13) &
Chr(10))
WHERE Background Like "*" & Chr(13) & "*";

Code 2:
UPDATE ReviewBackGrnd SET Background = IIf([Background] Is Not
Null,Replace([Background],Chr(13),Chr(13) & Chr(10)),Null) WHERE Background
Like "*" & Chr(13) & "*";

Thanks

 

[Apprentice]

Oh... I am using the code in an "update query"

 

[fredg]

I am trying two different lines of code to replace a common symbol that is
created in a memo field when text is cut from MS Word and pasted into the
field.

But instead of replacing the symbol, both are actually adding the symbol..
amaizing. Hope someone can help on this..

Code 1:
UPDATE ReviewBackGrnd SET Background = Replace(Background,Chr(13),Chr(13) &
Chr(10))
WHERE Background Like "*" & Chr(13) & "*";

Code 2:
UPDATE ReviewBackGrnd SET Background = IIf([Background] Is Not
Null,Replace([Background],Chr(13),Chr(13) & Chr(10)),Null) WHERE Background
Like "*" & Chr(13) & "*";

Thanks

If the symbol is chr(13) I think all you should need is:
UPDATE ReviewBackGrnd SET Background =
Replace(Background,Chr(13),Chr(13) & Chr(10))

If the field does not contain a chr(13) nothing will happen so there
should be no need to test for it.

HOWEVER, it may be that the square symbol is actually chr(10) (I don't
know what the new line value is in Word, but in Excel it is chr(10))
so it might be that you need:

UPDATE ReviewBackGrnd SET Background =
Replace(Background,Chr(10),Chr(13) & Chr(10))

Again, no need to test for chr(10).

 

[Apprentice]

Thanks Fred, I tried both, but that didn't get it.

Not sure why, any other seggestions?
--
Your guidance is greatly appreciated!

I am trying two different lines of code to replace a common symbol that is
created in a memo field when text is cut from MS Word and pasted into the
field.

But instead of replacing the symbol, both are actually adding the symbol..
amaizing. Hope someone can help on this..

Code 1:
UPDATE ReviewBackGrnd SET Background = Replace(Background,Chr(13),Chr(13) &
Chr(10))
WHERE Background Like "*" & Chr(13) & "*";

Code 2:
UPDATE ReviewBackGrnd SET Background = IIf([Background] Is Not
Null,Replace([Background],Chr(13),Chr(13) & Chr(10)),Null) WHERE Background
Like "*" & Chr(13) & "*";

Thanks

If the symbol is chr(13) I think all you should need is:
UPDATE ReviewBackGrnd SET Background =
Replace(Background,Chr(13),Chr(13) & Chr(10))

If the field does not contain a chr(13) nothing will happen so there
should be no need to test for it.

HOWEVER, it may be that the square symbol is actually chr(10) (I don't
know what the new line value is in Word, but in Excel it is chr(10))
so it might be that you need:

UPDATE ReviewBackGrnd SET Background =
Replace(Background,Chr(10),Chr(13) & Chr(10))

Again, no need to test for chr(10).

 

[fredg]

Thanks Fred, I tried both, but that didn't get it.

Not sure why, any other seggestions?

My guess then is that the square is not a chr(13) nor a chr(10) value.
I would suggest you check what the actual ascii value is of that
square character.
In the debug window, with the form open:
? Asc(Mid(forms!FormName!FieldName,10,1))

where the 10 represents the number of characters from the beginning of
the data in the field to the square.

 

[Apprentice]

ok Fred, I ran it and it came back with a "102". Then I changed to the code
below and tried it. The squares are still showing up and they changed
position a little. I'd like to have the code for 13, 10 and this new 102, it
that possible?

Thanks for your help and please hang with me.

UPDATE ReviewEXS SET ExecutiveSummary =
Replace(ExecutiveSummary,Chr(102),Chr(102) & Chr(10))
WHERE ExecutiveSummary Like "*" & Chr(102) & "*";

 

[fredg]

ok Fred, I ran it and it came back with a "102". Then I changed to the code
below and tried it. The squares are still showing up and they changed
position a little. I'd like to have the code for 13, 10 and this new 102, it
that possible?

Thanks for your help and please hang with me.

UPDATE ReviewEXS SET ExecutiveSummary =
Replace(ExecutiveSummary,Chr(102),Chr(102) & Chr(10))
WHERE ExecutiveSummary Like "*" & Chr(102) & "*";

chr(102) is the lower case f character.
You have most likely incorrectly counted the position that the square
is in.

Anyway, to change chr(102) to a chr(13) & chr(10) you would use:

UPDATE ReviewEXS SET ExecutiveSummary =
Replace(ExecutiveSummary,Chr(102),Chr(13) & Chr(10))

There is no need for a Where clause.

However, I would suggest you re-check the square's actual position in
the field and it's Ascii value. I doubt it's chr(102).