Results 1 to 9 of 9
  1. #1
    terrence7d is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2015
    Location
    UAE
    Posts
    4

    Creating a search form in Access to search multiple items from same field

    Hi All,



    I have created a search form in Access 2007 to search through specific fields but I need someone to help me out here

    how do I search for two or more records from the same field.

    Example: The field First Name has different names and I would like to search for three names , i.e Terrence, John and Sandra
    First Name Last Name
    Terrence Dsouza
    John Doe
    Daryl Collins
    Sandra Martha








    Any Help would be greatly appreciated.

    Thanks

    Terrence

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in the query, under the FIRSTNAME field
    in the criteria,
    'Terrence' or 'John' or 'Daryl'

    to do this from a form...
    have a 'Find' table, tFind.
    in it put the 3 names in the table.
    JOIN this table to your main data table , link on [FirstName]

    this will pull only those names. If you want to use a form and simply click on names to add to the tFind table, see below:

    (will add later...this wont allow me to upload)

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    build a form that has list box. Dbl-click the item to add it to the tFind table. Then run the query that joins your main data to the tFind table.
    Click image for larger version. 

Name:	pick state-lbl.png 
Views:	18 
Size:	34.2 KB 
ID:	21913

  4. #4
    terrence7d is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2015
    Location
    UAE
    Posts
    4
    Hi Ranman,

    Thank you for your reply and greatly appreciate your help , but this is not what I am looking for, I need to search many values from the same field separated by a parameter such as a PIPE or COMMA e.g : Terrence|John|Sandra|Steve here I have separated each value by a Pipe.

    you can see the form I have made in the screen shot below.

    Attachment 21918

    Thanks

    Terrence

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Requires VBA code. Review http://allenbrowne.com/ser-50.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    terrence7d is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2015
    Location
    UAE
    Posts
    4
    Hi June7,

    Thanks for your help , but this is not what I want. I have a textbox field where i enter the values I want to search. I am not working with a listbox.

    Thanks anyway ,Much appreciated

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    That has been discussed in other threads, now if I can only find one.

    What you have in the textbox is a single string of characters, not a set of values. What you need is a structure like:

    ("A","B","C") or ('A','B','C') - either should work

    That's what a listbox and Allen's code accomplishes.

    Converting your string into the set of values might be possible. Say you enter a string like: a,b,c

    Code to reconstruct:

    x = "('" & Replace([textbox], ",", "','") & "')"

    Or you can very carefully type into textbox the correct placement of parens, apostrophes, commas, values.

    Now use that structure to filter a report as shown in Allen's tutorial or to set the Filter property of a form.

    "[FirstName] IN " & x
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    terrence7d is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2015
    Location
    UAE
    Posts
    4
    Hi June7

    Sorry if I sound like a dumb person , but I am a newbie to access , I would like to know where exactly to enter the code you just provided could you be so kind enough to provide an example.

    Thanks

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Code behind a button Click event.

    I don't know your db so can't be specific. Adapt code examples already referenced to suit your needs.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 3
    Last Post: 09-02-2013, 04:33 PM
  2. Creating a search form in access
    By fawadmz in forum Access
    Replies: 2
    Last Post: 10-15-2012, 12:48 AM
  3. Replies: 7
    Last Post: 08-08-2012, 03:28 PM
  4. Replies: 12
    Last Post: 03-22-2012, 02:48 AM
  5. Creating an Access Search Form
    By Icky_Joe in forum Forms
    Replies: 2
    Last Post: 08-16-2010, 11:33 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