find if field contains a string (1 Viewer)

[cpampas]

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

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

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]

Try using more than one criteria. For example,

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

 

[jdraw]

Adjust your recordset and add an exclude table as shown in this article.

 

[Mike Krailo]

You could also use a regular expression to explicitly define a word boundry in your query. This assumes your search box is called txtSearchWholeWord.

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

This requires a function called regexp:

'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]

another approach is to add Same space to the Field:

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]

Thank you all for your help, I ended up going with arnelgp solution that works great