Parse data in string based on criteria in another column 1

[Dom606]

I have a single table of names and addresses. I have managed to parse the city, state, zip, etc data but now need a way to clean up the Address1 column.

Since all the records that need to cleaned up have the City name someplace in the Address1 column I thought I could select the records I need using this sql:

SELECT tblCandidate.Address1, tblCandidate.City
FROM tblCandidate
WHERE (((tblCandidate.Address1) Like "[city]" & "*"));

Obviously it does not work. Can anyone help me figure out how to select the Address1 records that have the City name in the string, then delete all data from the City moving right in the Address1 text string?

Table name tblCandidate Sample records. Only the 2nd and 2rd record would be selected

Address1 City
6501 Independence, Apt # 8105 Plano
6501 Nw 50th Lanegainesville, Fl 32653 Gainesville
6501 Sands Roadcrystal Lake, Il 60014 Crystal Lake
6502 21st Ave Tacoma



SELECT tblCandidate.Address1, tblCandidate.City
FROM tblCandidate
WHERE (((tblCandidate.Address1) Like "[city]" & "*"));

 

[PHV]

What about this ?

SELECT Address1, City
FROM tblCandidate
WHERE Address1 Like '*' & City & '*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dom606]

Hi PHV,
Thank you for the quick reply that did it. I guess I was close but no cigar.

I now have all the records selected, but how do I remove all the data from the Address1 column starting with the City?

 

[PHV]

Like this ?

UPDATE tblCandidate
Set Address1=Left(Address1,InStr(Address1,City)-1)
WHERE Address1 Like '*' & City & '*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dom606]

Wow! That worked great. You have saved me a ton of work.
Here is a star for your effort.
Dom