Need a Strategy

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a large table with three address fields. Any of the fields can
contain an abbreviation which I want to change: "Blvd" to "Boulevard," for
example. I have a smaller table with a list of abbreviations to change and
their preferred formats. I would like my command button to trigger an
automatic search & replace. The process needs to test each of three address
lines for each of the "wrong" abbreviations and insert the new term.

How do I juggle two tables to do this?

Thanks.
 
If it were me I'd do it with two loops. Pseudo Code:

Open RSOuterLoop Select Abrev, Fullname from tblAbrev
Do While not RSOuterLoop.EOF
Call ReplaceAll(RSOuterLoop.Abrev, RSOuterLoop.Fullname)
RSOuterLoop.MoveNext
Loop
Close RSOuterLoop

Sub ReplaceAll(strAbrev, strFullname)
Open RSInnerLoop Select addr1, addr2, addr3 from tblAddress
Do While not RSInnerLoop.EOF
Replace(RSInnerLoop.addr1, strAbrev , strFullname)
Replace(RSInnerLoop.addr2, strAbrev , strFullname)
Replace(RSInnerLoop.addr3, strAbrev , strFullname)
RSInnerLoop.MoveNext
Loop
Close RSOuterLoop
End Sub

I leave it to you to write the actual code that does the deed. However you
should be VERY careful about the strings you are replacing as they may give
unwanted results.


Ron W
www.WorksRite.com
 
Thanks, Ron. I'll give it a shot. I'll also heed your warning about
unwanted results, having long ago learned that St. Louis is a city and Street
Louis ain't!
 
Back
Top