Results 1 to 6 of 6
  1. #1
    ItsJustRey is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    4

    Question Search Query Based on Multiple Combo / Text Boxes

    I am trying to create a search form that has multiple UNBOUND combo and text boxes. Within the form is a subform named "SearchSubform" that displays the filtered/queried data. The SearchSubform's record source is a query i named "SearchQuery".

    When the user enters data from a form named "SearchForm", I want the data to be filtered correctly whether or not the user filled in some of the combo and text boxes or all of them.


    • I have a command button named "Search" that only has the following code:


    Private Sub Search_Click()
    SearchSubform.Requery
    End Sub


    • I have code to make sure all the fields are cleared on open:

    Private Sub Form_Open(Cancel As Integer)


    FacilitySearch = ""
    TypeOfDocumentSearch = ""
    ContractNumberSearch = ""
    ConsultantSearch = ""
    DescriptionSearch = ""
    LocationSearch = ""
    SearchSubform.Requery
    End Sub


    • I have individual buttons that clear the field(rest not included)

    Private Sub Clear1_Click()
    FacilitySearch = ""
    SearchSubform.Requery
    End Sub


    • I have a button that clear all fields:

    Private Sub ClearAll_Click()
    FacilitySearch = ""
    TypeOfDocumentSearch = ""
    ContractNumberSearch = ""
    DescriptionSearch = ""
    ConsultantSearch = ""
    LocationSearch = ""
    SearchSubform.Requery
    End Sub




    The following SQL code for my "SearchQuery" isnt properly displaying all the records that should display. Is there any way to improve this feature?

    SELECT [Document Catalog].[Document ID], [Document Catalog].Facility, [Document Catalog].[Type of Document], ([Contract Number 1] & ("[WO#"+[Work Order Number 1]+"]")) & ("("+([Project Number] & (" Task "+[Task Number]))+")") AS [Contract Number], [Document Catalog].Consultant, [Document Catalog].[Title of Document], [Document Catalog].[Title of Document 2], [Document Catalog].[Shelf/Drawer Number], [Document Catalog].[Box/Tube Number]
    FROM [Document Catalog]
    WHERE ((([Document Catalog].Facility) Like "*" & [Forms]![SearchForm]![FacilitySearch] & "*") AND (([Document Catalog].[Type of Document]) Like "*" & [Forms]![SearchForm]![TypeOfDocumentSearch] & "*") AND ((([Contract Number 1] & ("[WO#"+[Work Order Number 1]+"]")) & ("("+([Project Number] & (" Task "+[Task Number]))+")")) Like "*" & [Forms]![SearchForm]![ContractNumberSearch] & "*") AND (([Document Catalog].Consultant) Like "*" & [Forms]![SearchForm]![ConsultantSearch] & "*") AND (([Title of Document] & " " & [Title of Document 2] & " " & [Notes]) Like "*" & [Forms]![SearchForm]![DescriptionSearch] & "*") AND (([Shelf/Drawer Number] & " " & [Box/Tube Number]) Like "*" & [Forms]![SearchForm]![LocationSearch] & "*"))
    ORDER BY [Document Catalog].[Document ID];


    Thanks in advance!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You cant use form boxes in a query if there's nothing in them..so..
    Test all controls for a possible filter then build the where clause.

    Code:
    if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
    if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
    if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value
    
        'remove 1st And
    sWhere= mid(sWhere,4)
    
      'just use the filter
    
    me.filter = sWhere
    me.filterOn = true
    
       'OR   
       'apply the sql to the form
    
    sSql = "SELECT * FROM tblCompany WHERE " & sWhere

  3. #3
    ItsJustRey is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    4
    Where would I apply this code?

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    after user enters search data, click a search button
    in the onClick event

  5. #5
    ItsJustRey is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    4
    I'm not sure where we're going with this:

    Private Sub Search_Click()
    Dim sWhere As String
    If Not IsNull(Me.FacilitySearch) Then sWhere = sWhere & " and [Facility] = ' " & Me.FacilitySearch & " ' "
    If Not IsNull(Me.TypeOfDocumentSearch) Then sWhere = sWhere & " and [Type of Document] = ' " & Me.TypeOfDocumentSearch & " ' "
    If Not IsNull(Me.contractNumberSearch) Then sWhere = sWhere & " and [Contract Number] = ' " & Me.contractNumberSearch & " ' "
    If Not IsNull(Me.ConsultantSearch) Then sWhere = sWhere & " and [Consultant] = ' " & Me.ConsultantSearch & " ' "
    If Not IsNull(Me.DescriptionSearch) Then sWhere = sWhere & " and [Description] = ' " & Me.DescriptionSearch & " ' "
    If Not IsNull(Me.LocationSearch) Then sWhere = sWhere & " and [Location] = ' " & Me.LocationSearch & " ' "

    sWhere = Mid(sWhere, 4)

    Me.Filter = sWhere
    Me.FilterOn = True

    sSql = "SELECT * FROM DocumentCatalog WHERE " & sWhere
    SearchSubform.Requery
    End Sub

  6. #6
    ItsJustRey is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    4
    Is there any other way to fix my originally posted SQL code so all the correct queried records are displayed?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-22-2015, 12:47 AM
  2. Replies: 9
    Last Post: 06-04-2014, 10:45 AM
  3. Replies: 5
    Last Post: 08-05-2013, 09:47 AM
  4. Search records based on multiple check boxes
    By maytricks in forum Access
    Replies: 4
    Last Post: 01-14-2013, 07:26 PM
  5. Replies: 5
    Last Post: 03-07-2012, 12:57 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums