Removing part of a string 2

[Bronte1226]

I am trying to remove part of a string from each field that matches my criteria. The part that I want to remove is at the end of the string.(I am trying to remove somthing like "Apt 320" from a string that at the begining reads "First Street Apt 320&quot I have figured out how to select the part that I want, but istead of removing it, I am keeping that part! I have switched it around and need help turning this expression around the right way. Here is what I have so far:

Field: LocationStreetName
Table: Meters_11_8_02
Update To: Right([Meters_11_8_02]![LocationStreetName],6)
Criteria: Like "* Apt *" Or Like "* Unit *" Or Like "* Bldg *" Or Like "* Ste *"

I am open to different approches to the situation at hand as well. Can anyone help? Thanks!

 

[zerosandones]

You're on the right track, but, selecting the 6 right-most characters won't work since, when applied to "STREET NAME BLDG 123", it has incorrectly determined that the letter "D" is the cut-off point rather than the letter "B". The key is to look for the starting character position where the patterns " Apt ", " Bldg ", " Ste " or " Unit " can be found.

To strip off the unwanted trailing characters, use the following SQL:

SELECT Trim(Mid([LocationStreetName],1,Switch(InStr([LocationStreetName]," Apt &quot>0,InStr([LocationStreetName]," Apt &quot,InStr([LocationStreetName]," Bldg &quot>0,InStr([LocationStreetName]," Bldg &quot,InStr([LocationStreetName]," Ste &quot>0,InStr([LocationStreetName]," Ste &quot,InStr([LocationStreetName]," Unit &quot>0,InStr([LocationStreetName]," Unit &quot,True,Len([LocationStreetName])))) AS PrefixStreetName
FROM Meters_11_8_02;

When run, this SQL will produce a list of the leading parts of each location street name. Also, it made more sense to me that you were providing a consolidated list of all street names even if they don't contain any of the tested string patterns ... therefore, no filtering criteria is required.

In addition, if you're looking a unique list of street names, just add the word DISTINCT after the SELECT keyword in the above SQL.

Does this help?

00001111s

 

[danvlas]

Update To:

Left([Meters_11_8_02]![LocationStreetName], Len([Meters_11_8_02]![LocationStreetName])-Len([StringToReplace]))

Criteria:
Like "*" & [StringToreplace] And (Like "* Apt *" Or Like "* Unit *" Or Like "* Bldg *" Or Like "* Ste *&quot

That should work provided you have a [StringToReplace] shorter than [LocationStreetName]

Good luck

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[JeremyNYC]

What if someone spells out "apartment"? Even if you get a more complete list, what if someone spells out "apartmnet"?

Pretty difficult.

I'm afraid I don't ahve a great solution for you. This kind of data cleaning is really difficult. About the best thing I can offer is that you should probably be prepared to do this iteratively--make your sql and code work in such a way that you fix one group of records and then another and then another. Don't expect to be able to write one routine that does all of this for you.

And if you do happen to write it, make a lot of noise and sell it for a lot of money!

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.