Return Records that Contain Any of the Search Words

[SierraJuliet]

Anyone know how to return any records that contain any of the search words entered into a text box?

Example 1:

The search text box has multiple words entered: Dog Cat Apple Orange
If any of these words are contained in a single table being evaluated then return: True

Example 2:

The search text box has multiple words entered: Dog Cat Apple Orange
Each record returned contains one of the following: Dog, Cat, Apple, Orange

[orange]

See this search for some ideas.
Here is another thread that may be helpful.
Good luck with your project.

[CJ_London]

1. you will need a function to split your search textbox value into its components and compare with each field in your table
2. as 1

function might be something like

function contains(searchstr as string, fldval as variant) as boolean
dim sarr() as string
dim i as integer

sarr()=split(searchstr," ")
for i=0 to ubound(sarr)-1
    if fldval like "*" & sarr() & "*" then
        contains=true
        exit function
    end if
next i
end function

it gets more complicated if you want to exclude cattery, doghouse, concat, hotdog etc

how you want use it has not been made clear, but in a query you might have something like

select *
from mytable
where contains(searchstr,fld1) or contains(searchstr,fld2) or ....etc

[SierraJuliet]

Ajax,

This is what I have so far and it won't call the Function without error. Something to do with actually calling the function from the form.

Public Function iContains(RecSearchBox As String, FldVal As Variant) As Boolean
    Dim frmCurrentForm As Form
    Set frmCurrentForm = Screen.ActiveForm
    Dim FldValSplit As Variant
    Dim i As Integer
    Me.[txtRecordSearchBox] = RecSearchBox
    FldValSplit() = Split(RecSearchBox, " ")
    'SplitSearch = varSplit(i)
        For i = 0 To UBound(FldValSplit) - 1
            If FldVal Like "*" & FldValSplit() & "*" Then
                iContains = True
                Call frmCurrentForm.Name.[txtRecordSearchBox] 'returns to form to continue the process unique to each form
                'Exit Function
        End If
    Next i
End Function

[CJ_London]

you question was about returning records, not doing something with the records in your form so the function is something you would use in a query which would be the recordsource to your form. a fuller example you might use in the click event of a search button on a form

either

dim strSQL as string

strSQL="SELECT * FROM myTable WHERE contains(searchstr,fld1) or contains(searchstr,fld2)..."
me.recordsource=strSQL

or

dim strFilter as string
strFilter="contains(searchstr,fld1) or contains(searchstr,fld2)..."
me.filter=strFilter
me.filteron=true

[SierraJuliet]

I think I will try:

SELECT Split("This is the String I want to Split") AS SplitString;

[Micron]

If you use the Call statement, you must enclose arguments in parentheses (it sorta looks like you have arguments) but I don't see how you can use Call with this line
frmCurrentForm.Name.[txtRecordSearchBox]
because I can't see that as being a procedure call in the first place. Even so, if you have declared an object variable and set a form to it and are trying to return a value from a control on the form, why would you invoke the Name property? Your variable is an object, not a string, so why not frmCurrentForm.txtRecordSearchBox?

[SierraJuliet]

The following code is for placing inside the Form itself without using a Function (i.e. Module). The portion of the code specific to Split Array is bold. The remainder is provided for reference. The bold portion may be converted to a Function (i.e. Module); however, MS Access is finicky and will add Function portion later in another thread. Note: Less than two and more than two = '" & SearchArray(0) & "' Or '" & SearchArray(1) & "' will cause an error and will not search for entries properly.

Private Sub btnFindRecord_Click()
On Error GoTo btnFindRecord_Click_Err

btnFindRecord_Click_SplitSearch:
    If Me.[txtRecordSearchBox] <> "" Then
        Dim SearchBox As String
        Dim SearchArray() As String
        Dim i As Integer
        SearchBox = Me.[txtRecordSearchBox]
        SearchArray() = Split(SearchBox, " ", -1)
            For i = LBound(SearchArray, 1) To UBound(SearchArray, 1)
            Next i
        If (IsNull(DLookup("DataEntry", "tblDataEntry", "DataEntry = '" & SearchArray(0) & "' Or '" & SearchArray(1) & "'"))) Then
            GoTo btnFindRecord_Click_SearchRecord
        Else
            modDataEntry.Tracker "Invalid Data Entry Detected"
            Beep
            MsgBox ("Invalid Data Entry Detected.  Entry was blocked!"), vbOKOnly, "Warning"
            GoTo btnFindRecord_Click_Exit
        End If
    Else
        GoTo btnFindRecord_Click_Exit
    End If


btnFindRecord_Click_SearchRecord:
    Dim strSQL As String
    strSQL = "SELECT ID, DataEntry AS [Data], CreatedBy AS [Creator], DateCreated AS [Date], ModifiedBy As [Adjuster], LastModified AS [Last Modified] " _
            & "FROM tblDataEntry " _
            & "WHERE ID LIKE '*" & Me.txtRecordSearchBox & "*' " _
            & "OR DataEntry LIKE '*" & Me.[txtRecordSearchBox] & "*' " _
            & "OR CreatedBy LIKE '*" & Me.txtRecordSearchBox & "*' " _
            & "OR DateCreated LIKE '*" & Me.txtRecordSearchBox & "*' " _
            & "OR TimeCreated LIKE '*" & Me.txtRecordSearchBox & "*' " _
            & "OR ModifiedBy LIKE '*" & Me.txtRecordSearchBox & "*' " _
            & "OR LastModified LIKE '*" & Me.txtRecordSearchBox & "*' " _
            & "OR Notes LIKE '*" & Me.txtRecordSearchBox & "*' "
    Me.
[listSearch].RowSource = strSQL
    Me.
[listSearch].Requery

btnFindRecord_Click_Exit:
    Exit Sub

btnFindRecord_Click_Err:
    MsgBox Err.Description, vbOKOnly, ""
    Resume btnFindRecord_Click_Exit
End Sub

[SierraJuliet]

The following code is for when a user wants to use a Form and a Function (i.e. Module) for calling a Split Array.

The following is the Form code. The bold text is the portion that calls the Function (i.e. Module) for Split Array.

Private Sub btnFindRecord_Click()
On Error GoTo btnFindRecord_Click_Err

btnFindRecord_Click_SplitSearch:
    If Me.[txtRecordSearchBox] <> "" Then
        Call SplitSearch
    Else
        GoTo btnFindRecord_Click_Exit
    End If

btnFindRecord_Click_SearchRecord:
    Dim strSQL As String
    strSQL = "SELECT ID, DataEntry AS [Data], CreatedBy AS [Creator], DateCreated AS [Date], ModifiedBy As [Adjuster], LastModified AS [Last Modified] " _
            & "FROM tblDataEntry " _
            & "WHERE ID LIKE '*" & Me.txtRecordSearchBox & "*' " _
            & "OR DataEntry LIKE '*" & Me.[txtRecordSearchBox] & "*' " _
            & "OR CreatedBy LIKE '*" & Me.txtRecordSearchBox & "*' " _
            & "OR DateCreated LIKE '*" & Me.txtRecordSearchBox & "*' " _
            & "OR TimeCreated LIKE '*" & Me.txtRecordSearchBox & "*' " _
            & "OR ModifiedBy LIKE '*" & Me.txtRecordSearchBox & "*' " _
            & "OR LastModified LIKE '*" & Me.txtRecordSearchBox & "*' " _
            & "OR Notes LIKE '*" & Me.txtRecordSearchBox & "*' "
    Me.
[listSearch].RowSource = strSQL
    Me.
[listSearch].Requery

btnFindRecord_Click_Exit:
    Exit Sub

btnFindRecord_Click_Err:
    MsgBox Err.Description, vbOKOnly, ""
    Resume btnFindRecord_Click_Exit
End Sub

The following is the Function (i.e. Module) for executing the Split Array. Note: Less than two and more than two = '" & SearchArray(0) & "' Or '" & SearchArray(1) & "' will cause an error and will not search for entries properly.

Option Compare Database
Option Explicit


Public Function SplitSearch()
On Error Resume Next
    Dim frmCurrentForm As Form
    Set frmCurrentForm = Screen.ActiveForm
    Dim SearchBox As String
    Dim SearchArray() As String
    Dim i As Integer
    SearchBox = frmCurrentForm.[txtRecordSearchBox]
    SearchArray() = Split(SearchBox, " ", -1)
        For i = LBound(SearchArray, 1) To UBound(SearchArray, 1)
        Next i
    If (IsNull(DLookup("DataEntry", "tblDataEntry", "DataEntry = '" & SearchArray(0) & "' Or '" & SearchArray(1) & "'"))) Then
        Exit Function
    Else
        modDataEntry.Tracker "Invalid Data Entry Detected"
        Beep
        MsgBox ("Invalid Data Entry Detected.  Entry was blocked!"), vbOKOnly, "Warning"
        frmCurrentForm.btnFindRecord_Click_Exit
    End If
End Function