Results 1 to 2 of 2
  1. #1
    sam1603 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    1

    Searching a table based on multiple fields


    I'm trying to search a table using either a query or search box but I wish to search for multiple criteria within the table. So for example, if this was a table with first names and surnames, I may wish to search for 'Joe Smith' but the data would be in separate fields in the table being searched. Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in a continuous form that shows all the data, put some text or combo boxes to search in fields.
    build the 'where' clause by cycling thru all the controls....
    it executes after a find button CLICK event
    if null, ignore.
    if not, apply.


    Code:
    '----------------
    sub btnFilter_click()
    '----------------
    dim sWhere as string 
    
    
    sWhere = "1=1"
    if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
    if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
    if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"
    
    
    If sWhere = "1=1" Then
      Me.FilterOn = False
    Else
      Me.Filter = sWhere
      Me.FilterOn = True
    End If
    end sub

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

Similar Threads

  1. Replies: 2
    Last Post: 09-26-2017, 03:18 PM
  2. Replies: 1
    Last Post: 10-29-2016, 02:41 PM
  3. Searching multiple fields in a table
    By KyleMac in forum Programming
    Replies: 2
    Last Post: 12-11-2014, 02:20 PM
  4. Replies: 1
    Last Post: 07-23-2014, 02:52 PM
  5. Searching Using a Query in Multiple Fields
    By RossIV in forum Queries
    Replies: 10
    Last Post: 07-24-2013, 06:32 AM

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