Need help with Replace module and wildcards

[gojets1721]

I am using the below function which uses a table to replace values.

It works well except I need to add in wildcards and I'm unsure how to do it correctly.

Right now, if I want to replace 'Hello' with 'Hola', it works perfectly.

But I want it to replace any instance of hello, even if it is "Hello1" but it doesn't.

Any idea what to revise in order for it to do that?

Public Function ReplaceUDF(ByVal p As Variant) As Variant
    Const SQL As String = "SELECT * FROM tblReplacements ORDER BY Len([Replacement]) DESC;"
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    
    DBEngine.SetOption dbMaxLocksPerFile, 1000000
    
    ReplaceUDF = p
    If Len(p & "") < 1 Then Exit Function
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(SQL, dbOpenSnapshot, dbReadOnly)
    With rst
        .MoveFirst
        Do Until .EOF
            p = Replace$(" " & p & " ", " " & ![Original] & " ", " " & ![Replacement] & " ")
            p = Trim$(p)
            .MoveNext
        Loop
        .Close
    End With
    Set rst = Nothing
    Set dbs = Nothing
    ReplaceUDF = p
End Function

 

[CJ_London]

Remove the spaces in your replace statement

you would be better to use static rather dim for rst and dbs then you wouldn’t need to open it each time you call the function - and comment out the close and setting to nothing

 

[gojets1721]

Remove the spaces in your replace statement

Like this?

p = Replace$( & p & , & ![Original] & , & ![replacement] & )

I'm getting a syntax error when i do that

 

[gojets1721]

Nvm I'm dumb. I changed to this and it worked. I appreciate it!

Replace$("" & p & "", "" & ![Original] & "", "" & ![Replacement] & "")

 

[MajP]

I think we meant

Replace$(p,![Original],![Replacement] )

 

[ebs17]

I need to add in wildcards

Regular expressions provide pattern replacement.

Public Function RegExReplace(ByVal SourceText As String, _
      ByVal SearchPattern As String, _
      ByVal ReplaceText As String) As String
   Static oRegEx As Object
   If oRegEx Is Nothing Then Set oRegEx = CreateObject("Vbscript.Regexp")
   With oRegEx
      .Pattern = SearchPattern
      .IgnoreCase = True
      .Global = True
      .Multiline = True
      RegExReplace = .Replace(SourceText, ReplaceText)
   End With
End Function

' -----------------------------------------------

...
   p = RegExReplace(p, ![Original] & "*", ![Replacement] )
   ' p = RegExReplace(p, "\b" & ![Original] & "*?\b", ![Replacement] )   ' considers only words
...