Find/Replace Coding

[Guest]

I am trying to write code to find and replace text in my Geo field.

Currently, I have a table "Order" that contains a field called "Geo"- this
field could contain many states. Ex. Tri-State, FL, and CA.

My goal is to find any record that has Tri-State and replace it with the
following states: NJ, NY, MA. I want all this to happen through coding- i
don't want the user to have to go in and find/replace.

So the end result fo the example above would contain the following in the
Geo Field: NJ, NY, MA, FL, and CA

Any ideas/suggestions would be helpful.

 

[SusanV]

You can use the replace function
Replace("WhateverYourSearchingAsAString", "TakeOutThis","PutInThat"

Like:
myString = Replace("alphabet","a","o")

will give you myString = olphobet

An example in a module:

Dim rs As New ADODB.Recordset
Set cnx = CurrentProject.Connection
Dim text As String
Dim rep As String
Dim sq As String
Dim sql1 As String
sql1 = "Select * from tblPMO where Class = 'AE' ORDER BY RepNum"
rs.Open sql1, cnx, adOpenKeyset, adLockOptimistic, adCmdText
rs.MoveFirst

Do While Not rs.EOF
rep = rs.Fields("RepNum")
text = rs.Fields("Title")
text = Replace(text, "-", " - ")
text = Replace(text, "&", " & ")
sq = "UPDATE tblPMO SET Title = '" & text & "' WHERE " _
& "RepNum = '" & rep & "'"
DoCmd.RunSQL sq
rs.MoveNext
Loop

That particular bit adds spaces around dashes and ampersands.