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:
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:
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.