A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Finding unprintable characters in data



 
 
Thread Tools Display Modes
  #1  
Old April 27th, 2010, 04:20 PM posted to microsoft.public.access
Nich
external usenet poster
 
Posts: 10
Default Finding unprintable characters in data

I imported data which contained cr/lf or char(13). How do I find these
characters and strip them out?
  #2  
Old April 27th, 2010, 04:40 PM posted to microsoft.public.access
Kc-Mass
external usenet poster
 
Posts: 362
Default Finding unprintable characters in data

You look for CHR(13) and CHR(10) with instr() function to
find and left, right and mid functions to remove.

Regards

Kevin


"Nich" wrote in message
...
I imported data which contained cr/lf or char(13). How do I find these
characters and strip them out?



  #3  
Old April 27th, 2010, 05:01 PM posted to microsoft.public.access
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default Finding unprintable characters in data

First do a backup of the database just in case things go wrong.

UPDATE YourTable
SET YourTable.YourField
= Replace([YourTable]![YourField],Chr(13)," ");

The above will put a space in place of the Chr(13).

Also Chr(10) and Chr(13) often work together. You may have to find both.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Nich" wrote:

I imported data which contained cr/lf or char(13). How do I find these
characters and strip them out?

  #4  
Old April 27th, 2010, 05:08 PM posted to microsoft.public.access
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Finding unprintable characters in data

On Tue, 27 Apr 2010 08:20:02 -0700, Nich
wrote:

I imported data which contained cr/lf or char(13). How do I find these
characters and strip them out?


That's curious: I just answered an almost identical question from "Dave".
Here's what I told him, which you should be able to adapt:

You'll need to find out what it is. My guess is that it's a linefeed Chr(10) -
it wouldn't hurt to make a backup of your database just in case, and run a
query

SELECT memofield, InStr([memofield], Chr(10))
FROM tablename
WHERE memofield LIKE "*" & Chr(10) & "*"

to see if it shows the records with the box and the position of the linefeed.
Count characters and see if it matches.

You could then run an Update query updating the memo field:

UPDATE tablename
SET memofield = Replace([memofield], Chr(10), "")

Note that if the field has legitimate carriage return-linefeed characters this
will do more harm than good!

--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:41 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.