Replacing soft return in text field

[Sheila D]

Hi

I have about 3,000 contact names and addresses which have been imported to
Access from (unknown) external source. All text fields but data includes soft
returns which is causing me problems when I use in mail merge. Anybody know
how I can replace soft return with a space? Easy in Word but Access? I'm
using 2003. Any help much appreciated.

Sheila D

 

[Douglas J Steele]

Do you know what the ASCII for the soft return is? (It's probably Chr(10),
but it might be Chr(13)). Once you know that, you can use the Replace
function to replace whatever it is with " ".

 

[Sheila D]

Hi Douglas

You're right it's CHR(10) Tried using a query which pulled out all those
with CHR(10) when I uesd in criteria but when I tried using the Replace
function said it couldn't do then actually wiped out the complete address!! I
did of course make a back up 1st (which is why I'm laughing) Not sure how to
get this to work though, maybe I need VB code rather than update query? Any
thoughts. Thanks for your help

Sheila

 

[fredg]

Hi Douglas

You're right it's CHR(10) Tried using a query which pulled out all those
with CHR(10) when I uesd in criteria but when I tried using the Replace
function said it couldn't do then actually wiped out the complete address!! I
did of course make a back up 1st (which is why I'm laughing) Not sure how to
get this to work though, maybe I need VB code rather than update query? Any
thoughts. Thanks for your help

Sheila

What was the actual query SQL you used?

Update YourTable Set YourTable.[FieldName]
=Replace([FieldName],chr(10),chr(13) & chr(10));

should work.
Make another back-up first.

 

[Sheila D]

Hi Fred

This is the SQL I used
UPDATE CustomerDetails SET CustomerDetails.CUST_ADD =
Replace([CUST_ADD],Chr(10)," ",", ")
WHERE (((CustomerDetails.CUST_ADD) Like "*" & Chr(10) & "*"));

Seem to be some extra " and , put in by expression builder so have removed
them and now works fine, thanks very much

Sheila

Hi Douglas

You're right it's CHR(10) Tried using a query which pulled out all those
with CHR(10) when I uesd in criteria but when I tried using the Replace
function said it couldn't do then actually wiped out the complete address!! I
did of course make a back up 1st (which is why I'm laughing) Not sure how to
get this to work though, maybe I need VB code rather than update query? Any
thoughts. Thanks for your help

Sheila

What was the actual query SQL you used?

Update YourTable Set YourTable.[FieldName]
=Replace([FieldName],chr(10),chr(13) & chr(10));

should work.
Make another back-up first.

 

[Douglas J. Steele]

Not sure what that ", " at the end is for.

The syntax for the Replace function is:

Replace(expression, find, replace[, start[, count[, compare]]])


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Fred

This is the SQL I used
UPDATE CustomerDetails SET CustomerDetails.CUST_ADD =
Replace([CUST_ADD],Chr(10)," ",", ")
WHERE (((CustomerDetails.CUST_ADD) Like "*" & Chr(10) & "*"));

Seem to be some extra " and , put in by expression builder so have removed
them and now works fine, thanks very much

Sheila

Hi Douglas

You're right it's CHR(10) Tried using a query which pulled out all
those
with CHR(10) when I uesd in criteria but when I tried using the Replace
function said it couldn't do then actually wiped out the complete
address!! I
did of course make a back up 1st (which is why I'm laughing) Not sure
how to
get this to work though, maybe I need VB code rather than update query?
Any
thoughts. Thanks for your help

Sheila

:

Do you know what the ASCII for the soft return is? (It's probably
Chr(10),
but it might be Chr(13)). Once you know that, you can use the Replace
function to replace whatever it is with " ".

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi

I have about 3,000 contact names and addresses which have been
imported to
Access from (unknown) external source. All text fields but data
includes
soft
returns which is causing me problems when I use in mail merge.
Anybody
know
how I can replace soft return with a space? Easy in Word but Access?
I'm
using 2003. Any help much appreciated.

Sheila D

What was the actual query SQL you used?

Update YourTable Set YourTable.[FieldName]
=Replace([FieldName],chr(10),chr(13) & chr(10));

should work.
Make another back-up first.