Results 1 to 4 of 4
  1. #1
    AccessUser12345 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    2

    Creating a multi variable Search Form

    Hello,

    I am looking to create a form that will allow me to search by multiple fields (lets call these A B C D ….). I would like to be able to type a value for field A, and have the same form populate the other fields with matching records.
    In addition, I would like to be able to type in values for both fields A and B, and have the same form populate the other fields with matching records.
    I have created a search form that uses a search button and a query to crawl through records. Is this the best way to accomplish this?
    The issue I have come across deals with the fact that I have multiple empty records in multiple fields. This has prevented all relevant records from being returned. Only records with all fields filled were returned.


    I have used the Criteria: [Forms]![Search]![A] Or Like "*" & "*" . Upon opening the Query, I am asked to give parameter values for each field.
    Where do I go from here?

    Thanks

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Because your criteria references a form field, that form must be open when the query is run. The fact that the query is asking for parameter values for each field suggests you are closing the form before running the query.

    That said, the criteria you are using won't work. Like "*" will return every non-blank record, so using [Forms]![Search]![A] as well means nothing.

    It's also important to distinguish between AND and OR here. When you enter values in both fields [A] and [B] on the search form, do you want to see records that match BOTH values (AND) or that match EITHER ONE (OR)?

    Can you clarify some of these, please?

    It might be easier for you to construct a WHERE clause in your search button code, and then use that WHERE to select the records, rather than having the query reference form fields.

  3. #3
    AccessUser12345 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    2
    Thank you for your response.

    When I enter values in [A] and [B], I would like it to return all records with fields matching both A and B.

    How does one construct a "where clause"?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You use Google with How does one construct a "where clause"?
    Then read/review the results.

    Another good source is w3schools sql where



    Good luck.

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

Similar Threads

  1. Help With Multi Field Search Form
    By icaines9517 in forum Forms
    Replies: 6
    Last Post: 08-02-2016, 10:01 AM
  2. Multi-field search form
    By rebeldolphin69 in forum Forms
    Replies: 2
    Last Post: 06-15-2016, 05:28 AM
  3. Replies: 8
    Last Post: 09-02-2015, 03:00 PM
  4. Multi Use Search Form
    By CharlesWilliams in forum Forms
    Replies: 4
    Last Post: 03-13-2015, 01:09 PM
  5. Multi Select List Box on a Search Form
    By Vetgeorge in forum Forms
    Replies: 1
    Last Post: 09-28-2013, 08:11 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