Solved replace string with wildcard *

[silentwolf]

Hi again,

sorry guys cant seam to find something that works in my case.

I would love to replace a string with a "*" wildcard .

what I did try was this

Public Function ReplaceWuestenrot(ByVal strText As String) As String
    Dim strOld(0 To 1) As Variant
    Dim strNew As String
    Dim i As Integer

    strOld(0) = "W*stenrot"
    strNew = "Wüstenrot"

    For i = LBound(strOld) To UBound(strOld)
        strText = Replace(strText, strOld(i), strNew)
    Next i
    ReplaceWuestenrot = strText
End Function

but it does not work..

Would like to keep the rest of the string as it is just replace the word "Wuestenrot" or it could be "spellt wrong and be something like "Woastenrot"
so I like to find every possible way of it and change it to "Wüstenrot"

Could someone help please?

The above code only works when I write the correct spelling.


Many thanks

 

[The_Doc_Man]

It is not enough to toss in a wildcard character. You also need to use the LIKE operator to find such a string.

Like operator (Microsoft Access SQL)

Office VBA reference topic

docs.microsoft.com

However, in your case, it gets more complex because I don't recall that the REPLACE function accepts wildcard patterns and the online documentation seems to support that recollection.

Replace Function - Microsoft Support

support.microsoft.com

You can look up the use of Regular Expressions as a way to match against any of several possible related character sequences and RegEx functions are supported in queries.

Expressing basic Access query criteria as regular expressions

I'm familiar with Access's query and filter criteria, but I'm not sure how to express similar statements as regular expression patterns. I'm wondering if someone can help relate them to some easy

stackoverflow.com

Running Regex searches in an Access database

If you need to run a query in an Access database using regular expression, Renaud Bompuis has posted a great solution here. In your Access database, press ALT + F11 to enter Visual Basic. Enter the below vba code in a new module. It will create a new function that you can reference in a...

www.litigationsupporttipofthenight.com

 

[theDBguy]

Not sure if it applies; but also, maybe check out the SoundEx and Levenschtein functions.

 

[jdraw]

Do you have some more examples of the misspellings?
Your sample shows the final "stenrot" is consistent, so you could check for the city name like "w*stenrot" and replace with Wüstenrot.

You may get some ideas from this test.

Sub sometests()
    Dim goodname As String
    goodname = "Wüstenrot"
    Dim i As Integer
    Dim a(5) As String
    a(0) = "w*t"
    a(1) = "w*nrot"
    a(2) = "w*en*t"
    a(3) = "w*e*r*t"
    a(4) = "wu*t"
    a(5) = "w*n*at"

    For i = LBound(a) To UBound(a)
        If goodname Like a(i) Then
            Debug.Print "a(" & i & ")  " & a(i) & " is a match "
        Else
            Debug.Print "a(" & i & ")  " & a(i) & " is NOT a match "
        End If
    Next i
End Sub

Results:
a(0) w*t is a match
a(1) w*nrot is a match
a(2) w*en*t is a match
a(3) w*e*r*t is a match
a(4) wu*t is NOT a match
a(5) w*n*at is NOT a match

 

[silentwolf]

Hi guys,

many thanks to you for your replies!
@TheDocMan thanks for your links I will look into them! RegEx I knowbut never really used them.. or at least seam complicated to me just yet.
But will look into it and hope to get a better understanding of them!
Thanks!
@theDBguy, Oh never heard of it before I must admit.. need to look into it and see what that is. Thanks for your suggestion.

@jdraw

Do you have some more examples of the misspellings?

well not really at present as there where misspelling as said like WAostenrot, and Wuestenrot, as sometimes they change the german "Ü" to "UE" .
Could of course at some occactions be completly different but that I can not forsee and is not necessary.
But at least something like what happend now and with the "Umlaut" as we call the "Ü" so that will be taken care of.

But I see your example and I am sure I will get something useful out of it!

Many thanks to you for this example!

Cheers again to all!

Have a great Sunday )

 

[silentwolf]

Hi guys,

I just have done it like this now.

Sure not the elegant way of doing it but it works

With RegEx I still need to look further into it as I have still troubles with getting the rest of the string displayed.

What I mean is that it is difficult for me at least that I get an Regular Expression to change a substring and display all other words before and afterwards.

String"Wuestenrot Versicherungs-AG something Else"

To use RegEx to change the string to"Wüstenrot Versicherung-AG something Else"

Need to take more time to find something that explains that better to me. There are many Examples of how to change a Word but not sure how to put it all together just yet.

But at least I did get it to manage with a simple UDF like that

Function UpdateWuestenrot(strText As String) As String
    If strText Like "W*stenrot*" Then
        Dim intpos As Integer
        intpos = InStr(strText, " ")
            Dim strOld As String
            strOld = Left(strText, intpos - 1)
            UpdateWuestenrot = Replace(strText, strOld, "Wüstenrot")
        Else
        UpdateWuestenrot = strText
    End If
End Function

But still appreciate all your links and your input I will take more time to get the grasp on it .)

Cheers

 

[theDBguy]

Hi guys,

I just have done it like this now.

Sure not the elegant way of doing it but it works

With RegEx I still need to look further into it as I have still troubles with getting the rest of the string displayed.

What I mean is that it is difficult for me at least that I get an Regular Expression to change a substring and display all other words before and afterwards.

String"Wuestenrot Versicherungs-AG something Else"

To use RegEx to change the string to"Wüstenrot Versicherung-AG something Else"

Need to take more time to find something that explains that better to me. There are many Examples of how to change a Word but not sure how to put it all together just yet.

But at least I did get it to manage with a simple UDF like that

Function UpdateWuestenrot(strText As String) As String
    If strText Like "W*stenrot*" Then
        Dim intpos As Integer
        intpos = InStr(strText, " ")
            Dim strOld As String
            strOld = Left(strText, intpos - 1)
            UpdateWuestenrot = Replace(strText, strOld, "Wüstenrot")
        Else
        UpdateWuestenrot = strText
    End If
End Function

But still appreciate all your links and your input I will take more time to get the grasp on it .)

Cheers

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[jdraw]

I'd be very cautious of this condition

If strText Like "W*stenrot*" Then

But it seems your

intpos = InStr(strText, " ")

will catch/limit longer text(s).

Good to see you have a working solution.

 

[silentwolf]

Hi guys,

@jdraw

I'd be very cautious of this condition

so not really a good solution or?

 

[silentwolf]

I have it combined with this UpdateQuery

Sub UpdateWuestenrotUmsatztext()
    Dim strSQL As String
    strSQL = "UPDATE qryLastschriften SET Umsatztext = UpdateWuestenRot([UMSATZTEXT])"
    CurrentDb.Execute strSQL, dbFailOnError
End Sub

further I did

If strText Like "W*stenrot *" Then

so it catches if a Word the space too just incase there would be something similar with a longer string

 

[jdraw]

What exactly is qryLastschriften? An Update query is to update a table???

 

[silentwolf]

Yes it is an update Query for a table

Well qryLastschriften is a query where I limit the filter the table and then update it.