Find And Replace

[BRANDON99]

Hi

I have a table with a text field in which I want to search and replace some of the text, so I have another table in which I have two fields, one is the word to find, the other the replacement. The problem I have is e.g. if I have say the words “tea” and “teapot”as words to be replaced, tea will be replaced with the correct word but this would also replace the tea part of teapot. So what I am looking for is a find(match all) and replace for only the whole word, not part of it.

Has anyone got the answer for this?

Many thanks

Stephen

 

[RickSpr]

I figure you must be using code to do this, so I'll talk about the solution in code.

Define what you mean by "whole word". Is it necessarily preceded by a space or start of line, and followed by a space or end of line, or could there be special characters before and/or after the word as well?

If you don't have to worry about special characters, this will do it:

Public Sub FindAndReplace()
    Const MainTable = "MainTable"  ' table containing field to edit
    Const SrchField = "SrchField" ' field containing text to be changed
    Const MatchTable = "MatchTable" ' table with find/replace words
    Const FindWord = "FindWord"     ' field containing word to find
    Const ReplaceWord = "ReplaceWord" ' field containing replacement word
    Dim db As DAO.Database
    Dim rstMain As DAO.Recordset, rstMatch As DAO.Recordset
    Dim s As String

    On Error GoTo ErrorHandler
    Set db = CurrentDb()
    Set rstMain = db.OpenRecordset(MainTable)
    Set rstMatch = db.OpenRecordset(MatchTable)
    Do While Not rstMain.EOF
        If Not IsNull(rstMain.Fields(SrchField)) Then
            rstMatch.MoveFirst
            s = rstMain.Fields(SrchField)
            Do While Not rstMatch.EOF
                Call ReplaceWords(s, rstMatch.Fields(FindWord), _
                    rstMatch.Fields(ReplaceWord))
                rstMatch.MoveNext
            Loop
            If s <> rstMain.Fields(SrchField) Then
                rstMain.Edit
                rstMain.Fields(SrchField) = s
                rstMain.Update
            End If
        End If
        rstMain.MoveNext
    Loop
    rstMain.Close
    rstMatch.Close
    Set rstMain = Nothing
    Set rstMatch = Nothing
    Set db = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Runtime error (" & Err.Number & "):" & vbCr _
        & Err.Description
    Set rstMain = Nothing
    Set rstMatch = Nothing
    Set db = Nothing
End Sub

Private Sub ReplaceWords(ByRef Text As String, FindWord As String, _
                        ReplaceWord As String)
    Dim i As Integer, s As String
    Dim strFind As String, strReplace As String

    s = " " & Text & " "
    strFind = " " & FindWord & " "
    strReplace = " " & ReplaceWord & " "
    i = InStr(s, strFind)
    Do While i > 0
        s = Left$(s, i - 1) & strReplace & Mid$(s, i + Len(strFind))
        i = InStr(i + Len(strReplace), s, strFind)
    Loop
    Text = Mid$(s, 2, Len(s) - 2)
End Sub

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[BRANDON99]

Thanks Rick

This worked just as I wanted.

I am not new to Access, but have not tried much codeing, could you recomend a book that is not to complicated, or another source that will help me

Thanks

Stephen

 

[RickSpr]

I'm afraid I can't. The books I learned Access from are no longer in print, and were for version 2 anyway.

Learning the VBA language isn't hard, especially if you've ever had a programming class. What takes time is learning the built-in functions of VBA, and the methods and properties of all the objects Access, DAO, and ADO provide for you to control them.

Try to find a book about Access that has a lot of material about VBA, DAO, and ADO. Second choice would be a book about programming VBA for Office.

You could also just wing it without a book. Many people learn better from hands-on experience.
- Try to understand event driven programming first. It's the framework for everything you do in VBA.
- Take bits of code from the forums, FAQs, and help files and try to understand them, looking up unfamiliar words in the Help file to find out what they do. Look them up in the Object Browser too, and learn which library they belong to (at the bottom, the first word after "Member of").
- Don't try to memorize everything, just try to associate a method, property, or function name with what it does. Before too long, you'll be able to read a long procedure and understand how it works in general.
- Use the [green]See Also[/green] and [green]Applies To[/green] links in the Help topics, but use them lightly so you don't get overwhelmed.
- Play with writing tiny bits of code at first. A good way to do this is to look for questions on Tek-Tips that sound like something you're familiar with. By helping somebody else, you'll strengthen your own understanding.
- When you get so that you can follow a bigger chunk of sample code pretty well, delve into writing more complicated code, looking up the arguments for functions, properties and methods as you need them. This is tedious and slow, but with practice you'll find you're memorizing them without effort.

You have to kind of nibble away at understanding an object model (Access Forms, DAO or ADO), and then one day you "get it" and you feel its power become available to you.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein