How to replace certain characters in the whole table at once?

[frankt68]

Hi!

I have a table that contains certain characters, which should be replaced with others (such as "è"" with "c" or "§" with "z"). I do not know much about Access, but I know that a single character in single field can be replaced by using a query. I would like to know whether it is possible to replace all the characters in the whole table at once?
For example, in a table with five columns (fields), how can I replace 5 different, individual characters with another character in all columns at once?

 

[NickHa]

This should do what you need.

Private Sub fixCharacters()
Dim rst As Recordset, fld As Field
Dim varCharsIn As Variant, varCharsOut As Variant
Dim strSplit() As String
Dim i As Integer
Rem set up an array of characters to be replaced
varCharsIn = Array("è", "§")
Rem set up array of replacement characters - same relative positions as above array
varCharsOut = Array("c", "z")
Set rst = CurrentDb.OpenRecordset("SELECT * FROM [table];")
rst.MoveFirst
Do Until rst.EOF
  For Each fld In rst.Fields
    Select Case fld.Type
    Rem do this for text fields only
    Case dbText
         Rem ensure field is not null
         If Nz(fld, vbNullString) <> vbNullString Then
           Rem step through the input array to check for matches in field
           For i = 0 To UBound(varCharsIn)
             Rem split on the target character
             strSplit = Split(fld, varCharsIn(i))
             Rem if the split array has only one dimension,there's no match
             If UBound(strSplit) > 0 Then
               Rem found a match
               rst.Edit
               Rem rejoin the array using the corresponding replacement character
               rst.Fields(fld.Name) = Join(strSplit, varCharsOut(i))
               rst.Update
             End If
           Next
         End If
    Case Else
    End Select
  Next
  rst.MoveNext
Loop
End Sub

Put this procedure into a module and run it (but make a copy of the table first, in case you need to restore it). You need to change the SELECT statement to point to your table name - where it says

 

[JANR]

An alternative is to use the Replace() function if you run Access 2000 and newer.

Function FixTable()
Dim fld As DAO.Field
Dim db As DAO.Database
Dim strSQL As String
Dim tbl As String

Set db = CurrentDb()
tbl = "Mytable"

For Each fld In db.TableDefs("[" & tbl & "]").Fields
    If fld.Type = dbText And Not IsNull(fld) Then
        strSQL = "Update [" & tbl & "] Set [" & fld.Name & "]= Replace([" & fld.Name & "],'§','z')"
        DoCmd.RunSQL strSQL
        strSQL = "Update [" & tbl & "] Set [" & fld.Name & "]= Replace([" & fld.Name & "],'è','c')"
        DoCmd.RunSQL strSQL
    End If
Next
End Function

JR

 

[frankt68]

NickHa and JANR!

Thank you both for your answers. I'll try both solutions and I'll inform you if both of them are working.

 

[Savannah03]

Hi NickHa and JANR

I need to to do a similar search and replace to udate things like St to Street and Ave to Avenue . I have never enter any code in the module. Am I do understand that either of the code that you suggested just needs to be entered into the module and then run. I thought I had to use a query. If I get this working I would like to then create a macro so that it can be repeated when needed.

 

[Galaxiom]

Savannah:
JANR's code is a loop through the fields in each table of the database. It constructs a custom query for each replacment character for each field in each table and then runs it over an over again.

Nick's code opens a recordset on a table then works through it records by record and field by field looking for the characters. He could have used the Replace function for this step in this approach too. A further step would be to open all the tables in the database in a loop and apply the code to them.

There are several ways to do that particular job but they all involve loops that work though the database and some looping is required if you want to automate the task.


For a one off fix you might as well just run queries with the Replace function to change your terms. Note with St to Street you will need to replace " St " with " Street ". Don't forget the spaces or you will end up with a big mess.

You really should try to get some understanding of any code before you run it. JANR's code isn't too difficult to grasp and is a clear example of a typical loop. In your case you would NOT want it to do a loop trough all the database and that code might well do serious damage if you run it without understanding what it is going to do.

Most developers would probably develop the code further if it was to be used regularly. One enhancement would be to store the substituted terms in a table or other file and include code to loop through that list.

 

[Savannah03]

Thanks, I think that using the Update Query will work for me, but I am having trouble getting it to work correctly. I have enter the following in the Criteria, Like "* Ave *" and the results are all records where the street address contain "Ave". I then tried entering "* Avenue *". I get a message saying that "This event has been blocked or disable". Any suggestions?

 

[Savannah03]

I would like to use the Update Query to replace text within an address field. I am having trouble trying to figure out how to type what needs to be replace. I have entered Like "* Ave *" in the criteria and "* Avenue *" in the Update field. It didn't work. I also tried entering " Avenue " and that didn't work either. Any ideas?

 

[Galaxiom]

The event is blocked is due to the database being opened without giving permission to run queries that will chenge the database and macros. Open it again and approve the security or put your database in a Trusted Location if for Access 2007-10.