Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Unanswered: HELP please! Find and replace query

    ... self-taught MS Access user, not familiar with VB but know my way around the Query design template.

    I would like to create a query to update parts of a field. I have a text field from which I would like to remove "of" and "in" and "the". In reality I have a whole list of prepositions I need to remove but this is just an example.

    For example: My table contains a field called [caption] where I would like would like to change the contents of a record from "Guido in the house of Guiseppe the Knee in Sydney" to "Guido house Guiseppe Knee Sydney"

    Find and replace works just fine, but I need to run it repeatedly for each of the words I want to remove. I would therefore like to create a query which I can save for future use which lists all the various words I want to remove.

    Any help would really be appreciated!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can nest
    Code:
    REPLACE(REPLACE(myColumn, " the ", " "), " in ", " ")
    You need to get a bit cuter though for:
    "The house of Guiseppe the Knee in Sydney"
    or
    "house of Guiseppe the Knee in"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2009
    Posts
    3
    Thanks so much for your time!

    I follow the logic, but am battling to know where to use the above expression. Bit like a kid with a hammer!

    I assumed that I needed to replace myColumn with the fieldname I wanted to modify. So my expression read: REPLACE(REPLACE([Caption], " the ", " "), " in ", " ")

    I tried pasting it into the Query design template in the "Update to" section for the field which I am trying to modify and got a message that I am trying to use an invalid operator.

    I don't think I am using this expression in the right place.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you go to the SQL view, we can then see what's going on. Should be sommat like:
    Code:
    UPDATE myTable
    SET myColumn = REPLACE(REPLACE(myColumn, " the ", " "), " in ", " ")
    WHERE myCOlumn LIKE "* the *" OR myColumn LIKE "* in *"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2009
    Posts
    3
    Champion!

    Thank you very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •