Updating A Text String In A Text Field With An Update Query

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Sheldon
    #1

    Updating A Text String In A Text Field With An Update Query

    If I have the following string in a text field of a table:
    "This is a test"
    can I create an update query that will change, say, the string "is" to
    "xyz" in the two locations where it occurs or would this require VBA?
    Thanks,
    Sheldon Potolsky
    Tags: None
    • Allen Browne
      #2
      Re: Updating A Text String In A Text Field With An Update Query

      Use an Update query.

      Something like this:
      UPDATE Table1
      SET [F1] = Replace([F1], "is", "xyz")
      WHERE [F1] Like "*is*";

      --
      Allen Browne - Microsoft MVP. Perth, Western Australia
      Tips for Access users - http://allenbrowne.com/tips.html
      Reply to group, rather than allenbrowne at mvps dot org.

      "Sheldon" <SHPsalm139@aol .comwrote in message
      news:61e21fd7-b684-4cbb-9c37-fcd715600090@y3 8g2000hsy.googl egroups.com...
      If I have the following string in a text field of a table:
      "This is a test"
      can I create an update query that will change, say, the string "is" to
      "xyz" in the two locations where it occurs or would this require VBA?
      Thanks,
      Sheldon Potolsky

        Comment

        • Sheldon
          #3
          Re: Updating A Text String In A Text Field With An Update Query

          Thank you Allen; that worked great.

          As a followup, if I also wanted to change all occurrences of "t" or
          "T" to "123" in the string "This is a test", would I need an
          additional query? In my real world example, I'm trying to remove
          certain strings such as "<br/>" and "&nbsp;" whenever they occur in a
          closing description of a problem.

          Thank you, Sheldon
          >
          Something like this:
              UPDATE Table1
              SET [F1] = Replace([F1], "is", "xyz")
              WHERE [F1] Like "*is*";
          >
          --
          Allen Browne - Microsoft MVP.  Perth, Western Australia
          Tips for Access users -http://allenbrowne.com/tips.html
          Reply to group, rather than allenbrowne at mvps dot org.
          >
          "Sheldon" <SHPsalm...@aol .comwrote in message
          >
          news:61e21fd7-b684-4cbb-9c37-fcd715600090@y3 8g2000hsy.googl egroups.com...
          >
          >
          >
          If I have the following string in a text field of a table:
          "This is a test"
          can I create an update query that will change, say, the string "is" to
          "xyz" in the two locations where it occurs or would this require VBA?
          Thanks,
          Sheldon Potolsky- Hide quoted text -
          >
          - Show quoted text -

            Comment

            • Allen Browne
              #4
              Re: Updating A Text String In A Text Field With An Update Query

              Yes: execute an Update query for each replacement.

              --
              Allen Browne - Microsoft MVP. Perth, Western Australia
              Tips for Access users - http://allenbrowne.com/tips.html
              Reply to group, rather than allenbrowne at mvps dot org.

              "Sheldon" <SHPsalm139@aol .comwrote in message
              news:95a6d1ef-cbf5-4b77-9b7f-72f1a0c88b84@i7 6g2000hsf.googl egroups.com...
              Thank you Allen; that worked great.

              As a followup, if I also wanted to change all occurrences of "t" or
              "T" to "123" in the string "This is a test", would I need an
              additional query? In my real world example, I'm trying to remove
              certain strings such as "<br/>" and "&nbsp;" whenever they occur in a
              closing description of a problem.

              Thank you, Sheldon
              >
              Something like this:
              UPDATE Table1
              SET [F1] = Replace([F1], "is", "xyz")
              WHERE [F1] Like "*is*";
              >
              "Sheldon" <SHPsalm...@aol .comwrote in message
              >
              news:61e21fd7-b684-4cbb-9c37-fcd715600090@y3 8g2000hsy.googl egroups.com...
              >
              >
              >
              If I have the following string in a text field of a table:
              "This is a test"
              can I create an update query that will change, say, the string "is" to
              "xyz" in the two locations where it occurs or would this require VBA?
              Thanks,
              Sheldon Potolsky- Hide quoted text -
              >
              - Show quoted text -

                Comment

                Previous template Next
                Working...