find/replace with reference to list

[michael.boucher]

Searching the usergroups, I found code that I think might work, but I
wanted to explain exactly what I want to do before I start messing
with the code.

I have addresses that need to be standardized. I have a table of typos
and corrections. Specifically:

Table "Address"
Field: "Address1"
100 Randlett Streeet
40 Park Road
40 Park Rad
Apt 5 Saratoga Aveee

Table "Fix Address"
Field "typos" / Field "correction"
Streeet St
Road Rd
Rad Rd
Aveee Ave

(Note: my table "fix address" has about 400 records in it. This is why
I don't want to do the find/replace function)

How can I get Access to look at Typos, find it in Address1, and then
replace the typo with the correction version?

Below is the code I found that I think gets at what I want to do, but
actually does a little more than I need it to do. Any thoughts? A
search for " How do I use part of a field to lookup a code in another
table" will take you to where I found the code, from Ken Snell:



---------------------------------------------
Create a public function in a regular module.

Public Function ReplaceTheLetters(strOriginal As String) As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strTemp1 As String, strTemp2 As String
strTemp1 = strOriginal
ReplaceTheLetters = strOriginal
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Explanation", dbOpenDynaset, dbReadOnly)
Do While rst.EOF = False
strTemp2 = Replace(strTemp1, rst.Fields("Code").Value, _
rst.Fields("Active").Value, 1, -1, vbTextCompare)
If strTemp2 <> strTemp1 Then
ReplaceTheLetters = strTemp2
Exit Do
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Function

Then create an update query, which when run will update your data:

UPDATE [Order]
SET [Order].[Ordercode] = ReplaceTheLetters([Order].[Ordercode]);

Also, do not use Order as the name of a table. It and many other words
are
reserved words in ACCESS, and can create serious
confusion for ACCESS and Jet. See these Knowledge Base articles for
more
information:

 

[KARL DEWEY]

I would just use an update query - no code necessary.
Put both tables in an update query.

Use criteria --
Like "*" & [typos] & "*"
In the Update To section use --
Replace([YourFieldName], [typos], [correction])

 

[michael.boucher]

Excellent. Thanks for your help! It's not perfect, it takes "Street"
and shortens it to "Steet" because "Str" is already on the list, but I
can work with that. A very simple solution. I appreciate it a lot.

I would just use an update query - no code necessary.
Put both tables in an update query.

Use criteria --
Like "*" & [typos] & "*"
In the Update To section use --
Replace([YourFieldName], [typos], [correction])

--
KARL DEWEY
Build a little - Test a little

Searching the usergroups, I found code that I think might work, but I
wanted to explain exactly what I want to do before I start messing
with the code.

I have addresses that need to be standardized. I have a table of typos
and corrections. Specifically:

Table "Address"
Field: "Address1"
100 Randlett Streeet
40 Park Road
40 Park Rad
Apt 5 Saratoga Aveee

Table "Fix Address"
Field "typos" / Field "correction"
Streeet St
Road Rd
Rad Rd
Aveee Ave

(Note: my table "fix address" has about 400 records in it. This is why
I don't want to do the find/replace function)

How can I getAccessto look at Typos, find it in Address1, and then
replace the typo with the correction version?

Below is the code I found that I think gets at what I want to do, but
actually does a little more than I need it to do. Any thoughts? A
search for " How do I use part of a field to lookup a code in another
table" will take you to where I found the code, from Ken Snell:

Public Function ReplaceTheLetters(strOriginal As String) As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strTemp1 As String, strTemp2 As String
strTemp1 = strOriginal
ReplaceTheLetters = strOriginal
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Explanation", dbOpenDynaset, dbReadOnly)
Do While rst.EOF = False
strTemp2 = Replace(strTemp1, rst.Fields("Code").Value, _
rst.Fields("Active").Value, 1, -1, vbTextCompare)
If strTemp2 <> strTemp1 Then
ReplaceTheLetters = strTemp2
Exit Do
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Function

Then create an update query, which when run will update your data:

UPDATE [Order]
SET [Order].[Ordercode] = ReplaceTheLetters([Order].[Ordercode]);

Also, do not use Order as the name of a table. It and many other words
are
reserved words inACCESS, and can create serious
confusion forACCESSand Jet. See these Knowledge Base articles for
more
information: