Need a Strategy

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Habercom - 05 Aug 2005 15:02 GMT

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.

Signature

David Habercom
University of Tennessee

Reply to this Message

Ron Weiner - 05 Aug 2005 16:18 GMT

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

> 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
[quoted text clipped - 6 lines]
>
> Thanks.

Reply to this Message

David Habercom - 05 Aug 2005 17:00 GMT

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!

Signature

David Habercom
University of Tennessee

Reply to this Message