Find and Replace special characters in a field

[bobbie.matera]

I have the system setup to import a DomainList.csv file into a table
called tblDmnLst. It contains a column called "NitchMarket" (datatype
= text 75 character) where the user has discribed the market for that
domain. I need to loop through those records and find any feild that
contains a "_" underscore or a "-" dash and replace those characters
with a "/" slash.

can anyone help me out with the VBA code to do this.
thanks in advance
Bobbie

[David Lloyd]

Bobbie:

One alternative would be to use an update query. For example:

UPDATE tblDmnLst
SET tblDmnLst.NitchMarket = Replace(Replace([NitchMarket],"_","/"),"-","/");

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
<bo***********@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
I have the system setup to import a DomainList.csv file into a table
called tblDmnLst. It contains a column called "NitchMarket" (datatype
= text 75 character) where the user has discribed the market for that
domain. I need to loop through those records and find any feild that
contains a "_" underscore or a "-" dash and replace those characters
with a "/" slash.

can anyone help me out with the VBA code to do this.
thanks in advance
Bobbie

[bobbie.matera]

Very cool David thanks.
How do I check for the "+" sign at the end of a string and remove it
(not replace). I don't want to leave any plus signs hanging out at the
end of the string.

Thanks again, Bobbie

[pietlinden]

bo***********@gmail.com wrote:

Very cool David thanks.
How do I check for the "+" sign at the end of a string and remove it
(not replace). I don't want to leave any plus signs hanging out at the
end of the string.

Thanks again, Bobbie

Right$(MyString,1)="+"

[bobbie.matera]

Right$(MyString,1)="+"
finds the + sign at the end but how do I remove it?
Can I incorporate that somehow into this SQL statement

UPDATE tblDmnLst
SET tblDmnLst.NitchMarket =
Replace(Replace([NitchMarket],"_","/"),"-","/");

Thanks

[David Lloyd]

Bobbie:

Removing the "+" at the end of the string would just require an additional
step. For example:

UPDATE tblDmnLst
SET tblDmnLst.NitchMarket =
Replace(Replace(IIf(Right([NitchMarket],1)="+",Left([NitchMarket],Len([NitchMarket])-1),[NitchMarket]),"_","/"),"-","/");

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
<bo***********@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
Very cool David thanks.
How do I check for the "+" sign at the end of a string and remove it
(not replace). I don't want to leave any plus signs hanging out at the
end of the string.

Thanks again, Bobbie

[datamystic]

You could also use a tool like DataPipe (www.datamystic.com) to
search/replace the fields in the database using a regex.