Remove Carriage Return symbols from data

[2Blessed4Stress]

I have a table where the data was imported from tables connected to another
MS Access database using ODBC connection. The data in one field, which is a
memo field, contains little square box carriage return symbols in it.

I've tried using an update qry like this:

Replace([DESCRIPTION],Chr(13) & Chr(10)," ")

to replace the symbols with spaces. It says it updates but it doesn't. Can
someone please explain a way to remove those squares from my data?

 

[Douglas J. Steele]

Are you sure that the squares are a carriage return followed by a line feed
(which is what Chr(13) & Chr(10) equates to)? Might they just be Chr(10), or
might they be tabs (Chr(9))?

Rather than trying to update the data, why not just include that Replace
function call in your Select query?

(to be honest, I didn't think it was possible to use ODBC to connect from
Access to a Jet database.)

 

[2Blessed4Stress]

I don't know if they are just Chr(10). I don't know how to tell. I do know
they are not tabs.
I want to update the table because it's used for many different qry's in the
database.

Are you sure that the squares are a carriage return followed by a line feed
(which is what Chr(13) & Chr(10) equates to)? Might they just be Chr(10), or
might they be tabs (Chr(9))?

Rather than trying to update the data, why not just include that Replace
function call in your Select query?

(to be honest, I didn't think it was possible to use ODBC to connect from
Access to a Jet database.)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a table where the data was imported from tables connected to another
MS Access database using ODBC connection. The data in one field, which is
a
memo field, contains little square box carriage return symbols in it.

I've tried using an update qry like this:

Replace([DESCRIPTION],Chr(13) & Chr(10)," ")

to replace the symbols with spaces. It says it updates but it doesn't.
Can
someone please explain a way to remove those squares from my data?

 

[yapi100]

2Blessed4Stress wrote on 06/25/2009 19:14 :

I don't know if they are just Chr(10). I don't know how to tell. I do know
they are not tabs.
I want to update the table because it's used for many different qry's in the
database.

Are you sure that the squares are a carriage return followed by a line feed
(which is what Chr(13) & Chr(10) equates to)? Might they just be Chr(10),
or
might they be tabs (Chr(9))?

Rather than trying to update the data, why not just include that Replace
function call in your Select query?

(to be honest, I didn't think it was possible to use ODBC to connect from
Access to a Jet database.)

Doug Steele, Microsoft Access MVP

(no private e-mails, please)


"2Blessed4Stress"
wrote in
message news:

I have a table where the data was imported from tables connected to another
MS Access database using ODBC connection. The data in one field, which is
a
memo field, contains little square box carriage return symbols in it.

I've tried using an update qry like this:

Replace([DESCRIPTION],Chr(13) & Chr(10)," ")

to replace the symbols with spaces. It says it updates but it doesn't.
Can
someone please explain a way to remove those squares from my data?

I have the followings in MS Access SQL and it works fine. Now instead of "a"
want to use a string wildcard but only for alpha numeric (a to z and A to Z)
How can it be done.

UPDATE Detail SET Artikel_Text = Replace(Artikel_Text,"a" & Chr(13)
Chr(10),"a")
WHERE Artikel_Text Like "*" & Chr(13) & "*";