Searching with multiple key words

lmryckman · March 10th, 2014, 03:15 PM

I have an unbound text box for users to enter key words to search within (1) field. On this form, there are (12) other fields that they can populte to narrow down their searches. For each field, I have a public function that sets the string based on what is in each field. This string is then called out in the query as criteria. This all works perfect except for being able to put in multiple key words.

So for instance if I have records where the field [2-BriefDescription] had the following values in 5 records:

1 The quick brown fox jumps high
2 Sally sells sea shells down by the sea shore
3 Peter piper picks a peck of pickled peppers
4 Mary had a little lamb
5 crazy sentances that include a fox, mary, peter, and sally

Serch criteria in the unbound text box is entered as:

sally fox

Returns the following records:
1 The quick brown fox jumps high
2 Sally sells sea shells down by the sea shore
5 crazy sentances that include a fox, mary, peter, and sally

Any ideas?

Here is additional information:
This is going to get lengthy...I want to make sure you have enough information to help...

SQL statement for the query:
SQL
SELECT tblTestRequestHeader.[2-BriefDescription], tblTestRequestHeader.[6-EntryDate],
tblTestRequestHeader.[4-AccountNumber], tblTestRequestHeader.[7-ProductCode], tblTestRequestHeader.[13-
Status], tblTestRequestHeader.[14-DateComplete], tblTestRequestHeader.[25-OriginatorName],
tblTestRequestHeader.[26-RequestorName], tblTestRequestHeader.[48-Technician], tblTestRequestHeader.[1-
LTRNumber], tblTestRequestHeader.[3-Priority], tblTestRequestHeader.[5-Requestor], tblTestRequestHeader.[8-
ProductDescription], tblTestRequestHeader.[9-TargetFinishDate], tblTestRequestHeader.[10-
DetailedTestInformation], tblTestRequestHeader.[11-Project], tblTestRequestHeader.[12-Originator],
tblTestRequestHeader.[15-Scope], tblTestRequestHeader.[16-Fluke], tblTestRequestHeader.[17-Open],
tblTestRequestHeader.[18-ForceGage], tblTestRequestHeader.[19-TorqueWrench], tblTestRequestHeader.[20-
PressureGage], tblTestRequestHeader.[21-StopWatch], tblTestRequestHeader.[22-Manometer],
tblTestRequestHeader.[23-FileLink], tblTestRequestHeader.[24-TestResultsSummary],
tblTestRequestHeader.[27-TempModule], tblTestRequestHeader.[28-FlowMeter], tblTestRequestHeader.[29-
Chamber], tblTestRequestHeader.[30-PressureModule], tblTestRequestHeader.[31-Burette],
tblTestRequestHeader.[32-TapeMeasure], tblTestRequestHeader.[33-PowerSupply], tblTestRequestHeader.[34-
ScopeID], tblTestRequestHeader.[35-FlukeID], tblTestRequestHeader.[36-ForceGageID],
tblTestRequestHeader.[37-TorqueWrenchID], tblTestRequestHeader.[38-PressureGageID],
tblTestRequestHeader.[39-StopWatchID], tblTestRequestHeader.[40-ManometerID], tblTestRequestHeader.[41-
TempModuleID], tblTestRequestHeader.[42-FlowMeterID], tblTestRequestHeader.[43-ChamberID],
tblTestRequestHeader.[44-PressureModuleID], tblTestRequestHeader.[45-BuretteID], tblTestRequestHeader.[46-
TapeMeasureID], tblTestRequestHeader.[47-PowerSupplyID]
FROM tblTestRequestHeader
WHERE (((tblTestRequestHeader.[2-BriefDescription]) Like strBriefDescription()) AND
((tblTestRequestHeader.[6-EntryDate]) Between dtInitiationDateFrom() And dtInitiationDateto()) AND
((tblTestRequestHeader.[4-AccountNumber]) Like strAccountNumberSearch()) AND ((tblTestRequestHeader.[7-
ProductCode]) Like strProductCode()) AND ((tblTestRequestHeader.[13-Status]) Like strStatus()) AND
((tblTestRequestHeader.[14-DateComplete]) Between dtCompletionDateFrom() And dtCompletionDateTo()) AND
((tblTestRequestHeader.[25-OriginatorName]) Like strOriginator()) AND ((tblTestRequestHeader.[26-
RequestorName]) Like strRequestor()) AND ((tblTestRequestHeader.[48-Technician]) Like strTechnician())) OR
(((tblTestRequestHeader.[2-BriefDescription]) Like strBriefDescription()) AND ((tblTestRequestHeader.[6-
EntryDate]) Between dtInitiationDateFrom() And dtInitiationDateto()) AND ((tblTestRequestHeader.[4-
AccountNumber]) Like strAccountNumberSearch()) AND ((tblTestRequestHeader.[7-ProductCode]) Like
strProductCode()) AND ((tblTestRequestHeader.[13-Status]) Like strStatus()) AND ((tblTestRequestHeader.[14-
DateComplete]) Is Null) AND ((tblTestRequestHeader.[25-OriginatorName]) Like strOriginator()) AND
((tblTestRequestHeader.[26-RequestorName]) Like strRequestor()) AND ((tblTestRequestHeader.[48-
Technician]) Like strTechnician()));

Code in module:

Code:

Function strRequestor() As String

    If Not IsNull(Forms!frmSearchScreen!cboRequestor.Value) Then

        strRequestor = Forms!frmSearchScreen!cboRequestor
    
    End If
    
    If IsNull(Forms!frmSearchScreen!cboRequestor.Value) Then
        
        strRequestor = "*"
        
    End If

End Function

Function strOriginator() As String
    
    If Not IsNull(Forms!frmSearchScreen!cboOriginator.Value) Then

        strOriginator = Forms!frmSearchScreen!cboOriginator
    
    End If
    
    If IsNull(Forms!frmSearchScreen!cboOriginator.Value) Then
        
        strOriginator = "*"
        
    End If
    
End Function

Function strTechnician() As String

    If Not IsNull(Forms!frmSearchScreen!cboTechnician.Value) Then

        strTechnician = Forms!frmSearchScreen!cboTechnician
    
    End If
    
    If IsNull(Forms!frmSearchScreen!cboTechnician.Value) Then
        
        strTechnician = "*"
        
    End If

End Function

Function strAccountNumberSearch() As String
    
    If Not IsNull(Forms!frmSearchScreen!cboAccountNumber.Value) Then

        strAccountNumberSearch = Forms!frmSearchScreen!cboAccountNumber
    
    End If
    
    If IsNull(Forms!frmSearchScreen!cboAccountNumber.Value) Then
        
        strAccountNumberSearch = "*"
        
    End If
    
End Function

Function strProductCode() As String

    If Not IsNull(Forms!frmSearchScreen!cboProductCode.Value) Then

        strProductCode = Forms!frmSearchScreen!cboProductCode
    
    End If
    
    If IsNull(Forms!frmSearchScreen!cboProductCode.Value) Then
        
        strProductCode = "*"
        
    End If
    
End Function

Function strPartNumber() As String

    If Not IsNull(Forms!frmSearchScreen!txtPartNumber.Value) Then

        strPartNumber = Forms!frmSearchScreen!txtPartNumber
    
    End If
    
    If IsNull(Forms!frmSearchScreen!txtPartNumber.Value) Then
        
        strPartNumber = "*"
        
    End If
    
End Function



Function strBriefDescription() As String

        If Not IsNull(Forms!frmSearchScreen!txtBriefDescription.Value) Then

        strBriefDescription = Forms!frmSearchScreen!txtBriefDescription
    
    End If
    
    If IsNull(Forms!frmSearchScreen!txtBriefDescription.Value) Then
        
        strBriefDescription = "*"
        
    End If

End Function

Function strStatus() As String

    If Not IsNull(Forms!frmSearchScreen!cboStatus.Value) Then

        strStatus = Forms!frmSearchScreen!cboStatus
    
    End If
    
    If IsNull(Forms!frmSearchScreen!cboStatus.Value) Then
        
        strStatus = "*"
        
    End If

End Function

Function dtInitiationDateFrom() As Date

    If Not IsNull(Forms!frmSearchScreen!txtInitiatedFrom.Value) Then

        dtInitiationDateFrom = Forms!frmSearchScreen!txtInitiatedFrom
    
    End If
    
    If IsNull(Forms!frmSearchScreen!txtInitiatedFrom.Value) Then
        
        dtInitiationDateFrom = "01/01/1940"
        
    End If
    
End Function


Function dtInitiationDateTo() As Date

    If Not IsNull(Forms!frmSearchScreen!txtInitiatedTo.Value) Then

        dtInitiationDateTo = Forms!frmSearchScreen!txtInitiatedTo
    
    End If
    
    If IsNull(Forms!frmSearchScreen!txtInitiatedTo.Value) Then
        
        dtInitiationDateTo = "12/31/2050"
        
    End If
    
End Function

Function dtCompletionDateFrom() As Date

    If Not IsNull(Forms!frmSearchScreen!txtCompletionDateFrom.Value) Then

        dtCompletionDateFrom = Forms!frmSearchScreen!txtCompletionDateFrom
    
    End If
    
    If IsNull(Forms!frmSearchScreen!txtCompletionDateFrom.Value) Then
        
        dtCompletionDateFrom = "01/01/1940"
        
    End If
    
End Function


Function dtCompletionDateTo() As Date

    If Not IsNull(Forms!frmSearchScreen!txtCompletionDateTo.Value) Then

        dtCompletionDateTo = Forms!frmSearchScreen!txtCompletionDateTo
    
    End If
    
    If IsNull(Forms!frmSearchScreen!txtCompletionDateTo.Value) Then
        
        dtCompletionDateTo = "12/31/2050"
        
    End If
    
End Function