match whole word in vba (1 Viewer)

lala

Registered User.
Local time
Today, 10:41
Joined
Mar 20, 2002
Messages
741
Hi, i need to be able to match the whole word in a field. i know that most of the time i can surround it by spaces and that will do it, but sometimes there's no space, comma or period only, also, sometimes the word is at the beginning or the end of the field.

thank you
 

SOS

Registered Lunatic
Local time
Today, 07:41
Joined
Aug 27, 2008
Messages
3,517
You can use Instr -
Code:
If Instr(1, Me.ControlNameHere, "Words Here") > 0 Then
   ' then it is true
Else
   ' then it is false
End If
 

lala

Registered User.
Local time
Today, 10:41
Joined
Mar 20, 2002
Messages
741
You can use Instr -
Code:
If Instr(1, Me.ControlNameHere, "Words Here") > 0 Then
   ' then it is true
Else
   ' then it is false
End If

can you please explain how the instr will know it's a separate word if it's not surrounded by spaces?

and thank you for your help
 

lala

Registered User.
Local time
Today, 10:41
Joined
Mar 20, 2002
Messages
741
Hello,

You can use wildcards such as the asterisk (*). For example if referring to a control such as in a query ...

Code:
Like "*" & [Forms]![fFormName]![txtControlName] & "*"

HTH,
-dK

this doesn't work for me because it will find it in the middle of words too and that's what i'm trying to avoid
 

dkinley

Access Hack by Choice
Local time
Today, 09:41
Joined
Jul 29, 2008
Messages
2,016
I mistook your post about "sometimes the word is at the beginning or the end of the field" as not being literal.

Also, I deleted my post after I reread your post and noticed the title ... the bit about in VBA and recognized I provided the wrong advice.

Apologies,
-dK
 

lala

Registered User.
Local time
Today, 10:41
Joined
Mar 20, 2002
Messages
741
I mistook your post about "sometimes the word is at the beginning or the end of the field" as not being literal.

Also, I deleted my post after I reread your post and noticed the title ... the bit about in VBA and recognized I provided the wrong advice.

Apologies,
-dK

not a problem at all, also, it doesn't have to be vba, query solution will work too

and thank you for trying
 

dkinley

Access Hack by Choice
Local time
Today, 09:41
Joined
Jul 29, 2008
Messages
2,016
I think SOS's solution is the best approach.

FYI ... you can see samples of it's use here. As SOS demonstrated, if the word exists, then you can commit to a sequence of steps. If not, then commit to a different sequence of steps.

What I posted is but one method used for filtering records in a Select query (and return all instances thus not meeting your requirements).

Note that the Instr function can also be used in a query.

-dK
 

lala

Registered User.
Local time
Today, 10:41
Joined
Mar 20, 2002
Messages
741
thank you. i know this function but i never knew you can use it to look for whole words. how will it know to only search whole words
 

lala

Registered User.
Local time
Today, 10:41
Joined
Mar 20, 2002
Messages
741
nope, doesn't work, it's picking up everything, just like i thought it would. i need it to only search for whole words.
 

c_smithwick

Underpaid Programmer
Local time
Today, 07:41
Joined
Jan 8, 2010
Messages
102
The Instr Function looks for the occurrence of one string within another. It doesn't know whole words from characters, but you can use it to determine the position of the occurrance. Instr(1, "Word", "ZZWordYY") will return 3, indicating that the searched term "Word" occurs beginning at the 3rd character position in the searched string. You will have to write a function to look at the searched for word in context. Play with the following to get you started. You might have to make some minor mods - it's "air code" and I haven't tested it.

Code:
Public Function wordInField(strTerm As String, strSearchString As String) As Boolean
Dim intPosition As Integer
Dim strNextOrPreviousCharacter As String

intPosition = InStr(1, strTerm, strSearchString)
Select Case intPosition
Case 0 'Word was not found in search string
    wordInField = False
Case 1 'Word found at the beginning of the search string
    strNextOrPreviousCharacter = Mid(strSearchString, Len(strTerm) + 1, 1) 'Look at character immediately following strTerm
    If strNextOrPreviousCharacter = " " Or strNextOrPreviousCharacter = "," Then
        wordInField = True
    Else
        wordInField = False
    End If
Case Else
    strNextOrPreviousCharacter = Mid(strSearchString, intPosition - 1, 1) 'Look at character immediately preceding strTerm
    If strNextOrPreviousCharacter = " " Then
        strNextOrPreviousCharacter = Mid(strSearchString, intPosition + Len(strTerm), 1) 'Look at character immediately following strTerm
        If strNextOrPreviousCharacter = " " Or strNextOrPreviousCharacter = "," Then
            wordInField = True
        Else
            wordInField = False
        End If
    Else 'found term is not a standalone word
        wordInField = False
    End If
End Select

End Function
 

lala

Registered User.
Local time
Today, 10:41
Joined
Mar 20, 2002
Messages
741
i'm not sure why i didn't reply to this 2 years ago, sorry.
i'm now again looking for a solution and will try to ply with your code and post back.

thank you!!
 

lala

Registered User.
Local time
Today, 10:41
Joined
Mar 20, 2002
Messages
741
ok, my main problem is how to do something like this.

replace([address], --list of separators, like .,;:/?-- & [replacewhat] & --list of separators, like .,;:/?-- ," " & [replacewith] & " ")


i don't know how to pass a list of characters that are separators at once instead running a replace function once for each COMBINATION of separators.
 

lala

Registered User.
Local time
Today, 10:41
Joined
Mar 20, 2002
Messages
741
in case anyone needs it, here's amazing code written by HansUp at StackOverflow
http://stackoverflow.com/questions/11728717/search-and-replace-whole-words-only/11729848#comment15565679_11729848



Code:
Public Function RegExpReplaceWord(ByVal strSource As String, ByVal strFind As String, ByVal strReplace As String) As String
' Purpose   : replace [strFind] with [strReplace] in [strSource] '
' Comment   : [strFind] can be plain text or a regexp pattern; '
'             all occurences of [strFind] are replaced '
    'requires reference to Microsoft VBScript Regular Expressions '
    'Dim re As RegExp '
    'Set re = New RegExp '
    'late binding; no reference needed '
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")

    re.Global = True
    re.IgnoreCase = True ' <-- case insensitve
    re.Pattern = "\b" & strFind & "\b"
    RegExpReplaceWord = re.Replace(strSource, strReplace)
    Set re = Nothing

End Function
 

Users who are viewing this thread

Top Bottom