Tags: access, characters, column, contains, csv, database, datatype, domainlist, field, file, import, microsoft, mysql, nitchmarket, oracle, setup, special, sql, system, tablecalled, tbldmnlst, text

Find and Replace special characters in a field

On Database » Microsoft Access

3,341 words with 6 Comments; publish: Mon, 31 Dec 2007 11:53:00 GMT; (25078.13, « »)

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

All Comments

Leave a comment...

  • 6 Comments
    • 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.

      <bobbie.matera.ms-access.todaysummary.com.gmail.com> wrote in message

      news:1127324305.991215.176140.ms-access.todaysummary.com.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

      #1; Mon, 31 Dec 2007 11:54:00 GMT
    • 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

      #2; Mon, 31 Dec 2007 11:55:00 GMT
    • bobbie.matera.ms-access.todaysummary.com.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)="+"

      #3; Mon, 31 Dec 2007 11:56:00 GMT
    • 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

      #4; Mon, 31 Dec 2007 11:57:00 GMT
    • 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.

      <bobbie.matera.ms-access.todaysummary.com.gmail.com> wrote in message

      news:1127339540.798073.291520.ms-access.todaysummary.com.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

      #5; Mon, 31 Dec 2007 11:58:00 GMT
    • You could also use a tool like DataPipe (www.datamystic.com) to

      search/replace the fields in the database using a regex.

      #6; Mon, 31 Dec 2007 11:59:00 GMT