removing undefined return character

  • Thread starter Thread starter news.telenet.be
  • Start date Start date
N

news.telenet.be

I have an access database which is converted from filemaker on macintosh. In
some fields at the end of the field I have some characters acting like
returns, but I don't know what they are. I want to delete them by replacing
them with nothing or with an update query but access doesn't seem to find
the characters. Is there a way to find those characters and replace them
with nothing. I want to trim the fields so they're gone. Whenever I try to
make a report they are very annoying.

Thanks in advance.

An example can be found here:
http://users.pandora.be/jochen.scheire/test.zip
 
As a guess the character is a carriage return, but not sure. Try

UPDATE MyTable
SET MyField = Left(MyField,Len(MyField)-1)
WHERE MyField Like "*" & Chr(13)

If that doesn't work then try Chr(10).
 
Ok, That seems to solve it partially. I first have a chr(10) but when
removed, I have to remove a CHR(13) then a chr(10) then a chr(13) and so on,
when I do that about 5 times, they are all gone. Problem is that I have this
problem in about 20 fields, is there a way to remove a chr(10) and chr(13)
and when they occur 5 times at the end of the file in one time?

Thx in advance
 
If you need to do this for a group of fields you might take a look at the
replace function, if you are using Access 2002 or later.

UpDate MyTable
Set
MyFieldA = Replace(Replace(MyFieldA,Chr(13),""),Chr(10),"")
MyFieldB = Replace(Replace(MyFieldB,Chr(13),""),Chr(10),"")
....

You will need to check the syntax for the Replace function. I did the above
from memory.

Or straight SQL if it is always five Chr(13) & Chr(10)

UPDATE MyTable
Set MyField = Left(MyField,Len(MyField)-10)
WHERE MyField Like "*" &
Chr(13)& Chr(10) &
Chr(13)& Chr(10) &
Chr(13)& Chr(10) &
Chr(13)& Chr(10) &
Chr(13)& Chr(10)

If you are using ACCESS 2000 you can use the replace function there also,
however you have to write a simple function to call the replace function - it
cannot be called directly in the SQL.
 
Back
Top