Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 10th, 2014, 03:15 PM
Registered User
 
Join Date: Feb 2014
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Searching with multiple key words

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Searching multiple tables melkin Classic ASP Databases 0 March 10th, 2008 11:48 AM
Searching for cyrillic words kostandin SQL Server 2000 1 September 1st, 2005 05:44 PM
search for multiple words keph Beginning PHP 5 April 6th, 2004 01:23 PM
macro for finding multiple words Lakshmi KS Access VBA 2 January 30th, 2004 02:42 AM
Selecting multiple words in Word 2000 jdsturm98 Beginning VB 6 0 September 3rd, 2003 03:49 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.