Solved

Query to remove text from a field based on text in another field

Posted on 2011-10-01
3
258 Views
Last Modified: 2012-05-12
My data looks bit  like this

[Field1]                                    [Field2]
Joe Bloggs                               Joe Bloggs Family Business
Joe Bloggs                               Joe Bloggs Farm
John Smith                               John Smith Removals
Smith & Son                             Smith & Son Grocers
Jones                                       Jones The Butchers
Jones, Smith & Bloggs             Jones, Smith & Bloggs Solicitors

I would like a query (preferably) or a function to remove the contents of Field1 from Field2 and Trim the results i.e.

[Field2]
Family Business
Farm
Grocers
The Butchers
Solicitors
0
Comment
Question by:Ludique
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36896372
Try this:

UPDATE SomeTable
SET Field2 = Trim(Replace(Field2, Field1, ""))
WHERE Field2 Like "*" & Field1 & "*"

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36896376
0
 

Author Closing Comment

by:Ludique
ID: 36896442
Lovely.  Quick and Simple.  Thank you.

Sorry, I didn't check out the link as well as Matthewspatrick's solution worked instantly.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question