Using Wildcards in Access DB Find and Replace Scenarios

[FFischer]

I modified some code I borrowed from the MS Northwinds Training DB as follows.

Sub ReplaceMe()
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim MyTable
MyTable = "Employees"
   Set MyDB = CurrentDb
   Set MyRS = MyDB.OpenRecordset(MyTable)
   MyRS.MoveFirst
   Do Until MyRS.EOF
    If MyRS![Job Title] = "Sales Representative" Then
         MyRS.Edit
         MyRS![Job Title] = "Marketing Representative"
         MyRS.Update
      End If
      MyRS.MoveNext
   Loop
End Sub

My goal is to perform find/replace functions much like in Excel. The above code works, however, I have not been able to incorporate wildcard statements, i.e, (In English) Find Sales* and Replace with Marketing*

Any help would be appreciated. Please don't bring up the discussion of Update queries.

Thanks

 

[FFischer]

I modified some code I borrowed from the MS Northwinds Training DB as follows.

Sub ReplaceMe()
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim MyTable
MyTable = "Employees"
   Set MyDB = CurrentDb
   Set MyRS = MyDB.OpenRecordset(MyTable)
   MyRS.MoveFirst
   Do Until MyRS.EOF
    If MyRS![Job Title] = "Sales Representative" Then
         MyRS.Edit
         MyRS![Job Title] = "Marketing Representative"
         MyRS.Update
      End If
      MyRS.MoveNext
   Loop
End Sub

My goal is to perform find/replace functions much like in Excel. The above code works, however, I have not been able to incorporate wildcard statements, i.e, (In English) Find Sales* and Replace with Marketing*

Any help would be appreciated. Please don't bring up the discussion of Update queries.

Thanks

Okay - For what it's worth, I solved the issue. Here is the code

Public Function ReplaceThings(MyTable As String, MyFind As String, MyReplace As String)
Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MyRSField As Object
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(MyTable)
Set MyRSField = MyRS![Job Title]
    MyRS.MoveFirst
        Do Until MyRS.EOF
            MyRS.Edit
            MyRSField = Replace(MyRSField.Value, MyFind, MyReplace)
            MyRS.Update
            MyRS.MoveNext
        Loop
    MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
End Function

Sub ReplaceEmployeeJobTitleThings()
Call ReplaceThings("Employees", " - ", " ")
End Sub

Now I am faced with a new hurdle. I can define [Job Title] as a RecordSet Field in the function. However, I would like to make it behave like the string elements in the Sub. If I use [Job Title] I get the message "External Name Not Defined". Has anyone ever been able to assign a name to something in square brackets?