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
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
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!
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!
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!