how to search & replace chr in multiple fields and records

N

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
 
R

raskew via AccessMonster.com

Hi -

In the criteria cell of the applicable field, enter:

InStr([FieldName],Chr(13))+InStr([FieldName],Chr(182))>0

HTH - Bob
 
K

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top