how to search & replace chr in multiple fields and records

[NewbiePete]

Hi

I've been cutting and pasting address information from web pages into
the relevant stakeholders' (SHs) records' address fields (SHs may have
multiple records identified by SHDetailsID), and I have inadvertantly
pasted in the html (e.g. <br /> or </td>, could be others) resulting
in an empty line (a paragraph mark when exported to Word) below the
pasted text.

I have created the following query from the grid which returns all the
records (~ 20 000) and relevant fields. I want to search for the above
(chr 13 or 182?) and delete the empty extra line in each one found:

SELECT DISTINCT tblStakeholders.StakeHolderID,
tblStakeholderDetails.SHDetailsID, tblStakeholders.SHFName,
tblStakeholders.SHLName, tblStakeholderDetails.StAddrLn1,
tblStakeholderDetails.StAddrLn2, tblStakeholderDetails.StAddrLn3,
tblStakeholderDetails.PoAddrLn1, tblStakeholderDetails.PoAddrLn2,
tblStakeholderDetails.PoAddrLn3, tblStakeholderDetails.Phone,
tblStakeholderDetails.Mobile, tblStakeholderDetails.Fax,
tblStakeholderDetails.email
FROM tblStakeholders INNER JOIN tblStakeholderDetails ON
tblStakeholders.StakeHolderID = tblStakeholderDetails.fStakeHolderID;

Any advice on the best way to go about this would be appreciated.

many thanks
peta

 

[Ken Sheridan]

You can use the Replace function to replace every occurrence of a string
expression with another value, in this case a zero length string. Are you
saying the Access values contain the tags as strings, i.e. "<br />" or
"</td>"? If so then its those strings you'd replace, e.g.

UPDATE tblStakeholders
SET SHFName = REPLACE(SHFName,"<br />",""),
SHLName = REPLACE(SHLName,"<br />",""),
<and so on to>
email = REPLACE(email,"<br />","");

If it’s the ANSI characters 13 and 182 which the Access value contain the
replace those, e.g.

UPDATE tblStakeholders
SET SHFName = REPLACE(SHFName,CHR(182),""),
SHLName = REPLACE(SHLName, CHR(182),""),
<and so on to>
email = REPLACE(email, CHR(182),"");

Be sure to back up the table first, however,

Ken Sheridan
Stafford, England