How to remove the CR character (â–¡)

[Peter Tyler]

I have imported large text fields from Excel into Access 2000 which
contain many repetitions of the Carriage Return character similar to
"â–¡".

How can I strip them out without doing it manually? The Find and
Replace function does not work as it does not recognize this character.

Thanks in advance for any advice.

Peter Tyler

[John Ortt]

I'm sorry I cant help here Peter but I will be interested in seeing your
replies.
I have a similar problem with an address field. In the past CR's were put
in to correctly format the field for print. It has now become necessary to
print the addresses at 30 degrees due to the envelope template we are using.
I have found a VB procedure which can do this but unlike Access it doesn't
recognise the "?" character return.
Ideally I would like to replace the "?" with a traditional CR symbol
(whatever that may be).

"Peter Tyler" <PL*****@gmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...
I have imported large text fields from Excel into Access 2000 which
contain many repetitions of the Carriage Return character similar to
"?".

How can I strip them out without doing it manually? The Find and
Replace function does not work as it does not recognize this character.

Thanks in advance for any advice.

Peter Tyler

[Ed Robichaud]

I do know that Word can find & replace that character, so you might try
cleaning up the text file in Word, then importing the text/csv file into
Access. Visual basic uses "vbCr" or "Chr(13)" to represent carriage
returns.
-Ed

"Peter Tyler" <PL*****@gmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...
I have imported large text fields from Excel into Access 2000 which
contain many repetitions of the Carriage Return character similar to
"?".

How can I strip them out without doing it manually? The Find and
Replace function does not work as it does not recognize this character.

Thanks in advance for any advice.

Peter Tyler

[rkc]

Peter Tyler wrote:

I have imported large text fields from Excel into Access 2000 which
contain many repetitions of the Carriage Return character similar to
"â–¡".

How can I strip them out without doing it manually? The Find and
Replace function does not work as it does not recognize this character.

Thanks in advance for any advice.

Try running an update query on a copy of the table using the replace
function and see how things go.

UPDATE Table
SET ImportField = Replace([ImportField],Chr$(13) ,"");

[Peter Tyler]

>>UPDATE Table
SET ImportField = Replace([ImportField],Chr$(13) ,"")

Thanks. Daunting, but I'll give it a try!

Peter

[Peter Tyler]

Thanks for your response Ed.

Peter

[Peter Tyler]

Tried it out in Word 2002 but it doesn't find the "â–¡" character nor
does it treat the character as a manual line feed.

Peter

[Peter Tyler]

OK, I've done it. I dropped the Access column with the offending text
(about 800 records) back into Excel and ran the Clean() function, which
strips out "non-printable" characters, then copied it back into Access.
Works a treat!

Thanks for everybody's input.

Peter Tyler