469,343 Members | 5,478 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,343 developers. It's quick & easy.

How to remove the CR character (□)

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

Nov 13 '05 #1
7 8251
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

Nov 13 '05 #2
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
Nov 13 '05 #3
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) ,"");
Nov 13 '05 #4
>>UPDATE Table
SET ImportField = Replace([ImportField],Chr$(13) ,"")

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

Peter

Nov 13 '05 #5
Thanks for your response Ed.

Peter

Nov 13 '05 #6
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

Nov 13 '05 #7
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

Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Hans Stoessel | last post: by
7 posts views Thread by Steve | last post: by
8 posts views Thread by Jan | last post: by
3 posts views Thread by Mark | last post: by
6 posts views Thread by Daniel Mark | last post: by
9 posts views Thread by Smiley | last post: by
26 posts views Thread by Brad | last post: by
4 posts views Thread by MC | last post: by
1 post views Thread by nyc680 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.