Replacing all "á" with blank

[JudyJones]

I have a linked text file that has a number of fields containing the following character that is mixed in with good data: á

I can manually accomplish what I want to do by using find and replace for all fields and asking for the á to be replaced with a space. Is there a way that can be automated other than using a separate query for each field?

[JohnH]

You could do it with a single update query, that updates each field using the Replace function.

[JudyJones]

I tried using the replace function and could not figure out how to get the query to search through multiple fields to accomplish the replacement. I have attached a sample database containing 5 records. My actual table has over 12,000 records. Could you show me how to use the replace function on all the fields?

[mbarron]

You can do a Find and Replace within the table itself. Open the table press Ctrl+H, enter the á in the Find What and leave the Replace With empty. Choose the entire table for the Look In and set the Match to Whole Field.

[JudyJones]

I know that I can do that process manually. What I am looking for is a way to accomplish it automatically through a query or code so that it will be just one step in an automatic process to prepare the file for exporting for another program to use.

[mbarron]

Try this:

Code: Select all

DoCmd.SetWarnings False
    Dim Rst 'As Recordset
    Dim f 'As Field

    Set Rst = CurrentDb.OpenRecordset("TempTable")

    For Each f In Rst.Fields
    If f.Type <> 4 Then
            DoCmd.RunSQL "update TempTable  set [" & f.Name & "] = """" where [" & f.Name & "] = ""á"""
    End If
    Next
    
    Rst.Close
DoCmd.SetWarnings True

[JudyJones]

That worked like a charm. Thank you very very much.