find if field contains a string (1 Viewer)

cpampas

Registered User.
Local time
Yesterday, 20:24
Joined
Jul 23, 2012
Messages
181
I want to find out if the string "Bolo" is contained in a text field in my recordset
and avoid the field "spagetti bolonhese", but I wanting to find the field
"bolo marble chocolate"

the folowing finds both
Code:
If rst(1) Like "*" & Term & "*" Then

so I tought I should add a space before and after the search string to avoid "spagetti bolonhese", but is finds neither of them

Code:
Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("tblProdutos", dbOpenDynaset)
    Dim n As Integer, str as string


    str="Bolo"
    str= " " & str & " "


    With rst
        While Not .EOF


            If rst(1) Like "*" & str & "*" Then
               ' here I do something
            End If


         .MoveNext
        Wend
    End With

Any toughts on how to find
Marble Bolo
Bolo Marble

and not to find :
Bolachas Mary
Mary Bolachas
Bolognese
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:24
Joined
Oct 29, 2018
Messages
19,184
Try using more than one criteria. For example,

Field Like "bolo *" OR Field Like "* bolo *" OR Field Like "* bolo"
 

Mike Krailo

Active member
Local time
Yesterday, 23:24
Joined
Mar 28, 2020
Messages
634
You could also use a regular expression to explicitly define a word boundry in your query. This assumes your search box is called txtSearchWholeWord.

Code:
iif(regexp([Product], "(\b" & me.txtSearchWholeWord & "\b)", True), [Product])

This requires a function called regexp:

Code:
'vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
' Purpose   :  Find records based on Regular Expression
' Usage     :  RegExp([Products].[Product], "/b" & mysearchstring & "/b", True)
' Requires  :  No Dependencies
' Inputs    :  Takes two arguments, the string and the regex and optionally the case
' Outputs   :  True or False
' CreatedBy :  http://www.icodeguru.com/database/access_hacks/0596009240/accesshks-chp-5-sect-16.html
'vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
'
Public Function RegExp(strString As String, _
                       strRegExp As String, _
                       Optional bolIgnoreCase As Boolean = False) As Boolean
    Dim re As Object
    Set re = CreateObject("vbscript.RegExp")
    re.Pattern = strRegExp
    re.IgnoreCase = bolIgnoreCase
    If re.Test(strString) Then
        RegExp = True
    Else
        RegExp = False
    End If
End Function
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:24
Joined
May 7, 2009
Messages
16,833
another approach is to add Same space to the Field:
Code:
Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("tblProdutos", dbOpenDynaset)
    Dim n As Integer, str As String


    str = "Bolo"
    str = " " & str & " "


    With rst
        While Not .EOF

            'arnelgp
            'add also space on Front and End of the field
            '
            If (" " & rst(1) & " ") Like "*" & str & "*" Then
               ' here I do something
               Debug.Print rst(1)
            End If


         .MoveNext
        Wend
    End With

Result:

Marble Bolo
Bolo Marble
 

cpampas

Registered User.
Local time
Yesterday, 20:24
Joined
Jul 23, 2012
Messages
181
Thank you all for your help, I ended up going with arnelgp solution that works great
 

Users who are viewing this thread

Top Bottom