Find and Replace on Multiple Matches (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 18:48
Joined
Sep 21, 2011
Messages
9,284
Yes, ideal. Especially as it's inevitably not as easy as I first thought... How would it be done in the function?
Replace that line I entered I coded in the previous post in the function.?
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:48
Joined
Sep 21, 2011
Messages
9,284
arnelgp gave you a function? fReplaceAllergens

in that function is a line
Code:
strInput = strInput & vbNullString
change that to
Code:
strInput = Replace(strInput, "'", "''") & vbNullString

then test again.
 

alexfwalker81

Member
Local time
Today, 10:48
Joined
Feb 26, 2016
Messages
90
arnelgp gave you a function? fReplaceAllergens

in that function is a line
Code:
strInput = strInput & vbNullString
change that to
Code:
strInput = Replace(strInput, "'", "''") & vbNullString

then test again.
Yes, the penny dropped after my initial confusion. That works brilliantly. Thanks to all who have contributed.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
, 02:48
Joined
May 7, 2009
Messages
14,564
which function are you using?
on my function i am Replacing only Whole Word (see i added space on the SQL criteria).
example:

"the<space>shrimp<space>" Will be replaced With "the<space>SHRIMP (blah blah)<space>"

if you want to replace (whether it is a Whole word or any part of the word)
you need to revised the function:

Code:
'''''''''''''''''''
'arnelgp
'
'''''''''''''''''''
Public Function fReplaceAllergens(ByVal strInput As Variant) As Variant
Dim strSQL As String
strInput = strInput & vbNullString
If Len(strInput) > 0 Then
    strSQL = "SELECT T.* FROM tblAllergenWords AS T " & _
            "WHERE INSTR(1,'" & strInput & "', T.[AllergenWord]) > 0"
    With CurrentDb.OpenRecordset(strSQL, _
            dbOpenSnapshot, dbReadOnly)
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            strInput = Replace(strInput, !AllergenWord, !AllergenWordReplacement)
            .MoveNext
        Loop
    End With
    
End If
fReplaceAllergens = strInput
End Function
 

alexfwalker81

Member
Local time
Today, 10:48
Joined
Feb 26, 2016
Messages
90
which function are you using?
on my function i am Replacing only Whole Word (see i added space on the SQL criteria).
example:

"the<space>shrimp<space>" Will be replaced With "the<space>SHRIMP (blah blah)<space>"

if you want to replace (whether it is a Whole word or any part of the word)
you need to revised the function:

Code:
'''''''''''''''''''
'arnelgp
'
'''''''''''''''''''
Public Function fReplaceAllergens(ByVal strInput As Variant) As Variant
Dim strSQL As String
strInput = strInput & vbNullString
If Len(strInput) > 0 Then
    strSQL = "SELECT T.* FROM tblAllergenWords AS T " & _
            "WHERE INSTR(1,'" & strInput & "', T.[AllergenWord]) > 0"
    With CurrentDb.OpenRecordset(strSQL, _
            dbOpenSnapshot, dbReadOnly)
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            strInput = Replace(strInput, !AllergenWord, !AllergenWordReplacement)
            .MoveNext
        Loop
    End With
   
End If
fReplaceAllergens = strInput
End Function
Excellent, I've made that amendment and it works like a charm - thank you!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:48
Joined
Oct 29, 2018
Messages
17,122
Excellent, I've made that amendment and it works like a charm - thank you!
Hi. Glad to hear you got it all sorted out. I was just curious, was the field you're trying to update a Memo/Long Text field? Just wondering... Thanks!
 

alexfwalker81

Member
Local time
Today, 10:48
Joined
Feb 26, 2016
Messages
90
Hi. Glad to hear you got it all sorted out. I was just curious, was the field you're trying to update a Memo/Long Text field? Just wondering... Thanks!
Yes, long text field. I got even more creative this morning by invoking the function twice. Once to do an initial pass which amended the allergens, and a second which then got rid of any duplicate words introduced by the first process!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:48
Joined
Oct 29, 2018
Messages
17,122
Yes, long text field. I got even more creative this morning by invoking the function twice. Once to do an initial pass which amended the allergens, and a second which then got rid of any duplicate words introduced by the first process!
Hi. Good job! Thanks for the update. Good luck with your project.
 

Users who are viewing this thread

Top Bottom