Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    drhassan is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    10

    A query based on user input in form

    I would like to use multiple parameters query bound to text fields and combo boxes in a search form. I want to let the users to search for any value in their records by optional fields. I have used the classical way for filtering my data through this criteria [forms]![name of the form]![fieldname],but with no success. If I have used one criteria alone the query's working fine.



    What is wrong with my query or what is wrong with me?

    Please see attached a screenshot for my query, hopefully to understand what's going wrong here?


    Thanks
    Attached Thumbnails Attached Thumbnails screenshot.png  

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    According to your query design, these conditions are being ANDed. Is that what you want? Or should these be ORs. If so,then the conditions mmust be placed on separate lines.

  3. #3
    drhassan is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    10
    Good point. All search fields are optional, and I've already tried to put two of those criteria in the ORs line, but the query came out with null values whenever I entered any inputs in the search field based on optional criteria.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What I 'm saying is all values must be present to get results

    My suggestion is to try with one one condition, get it working and then move to 2 conditions...

  5. #5
    drhassan is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    10
    No. Should be any input in any search field shows result. They are independent text fields.

    No way I tried your suggestion, but still I couldn't get right results.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Options:

    1. the parameterized query using LIKE operator with wildcard in criteria as you have set up should work, review http://www.datapigtechnologies.com/f...earchform.html
    However, null fields will be ignored, if any records will be null for those fields, might need to handle the nulls if you want to retrieve those records. Two ways to do that.
    a. LIKE "*" [Forms]![Searchform]![shipmententry] & "*" OR Is Null
    b. create fields with Nz() to convert the nulls and apply the wildcard criteria to those constructed fields.
    Nz([fieldname],"")

    2. dynamically construct filter criteria string and set form Filter property, review http://allenbrowne.com/ser-62code.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.

  7. #7
    drhassan is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    10
    I followed the steps of the first option that you suggested literally with no success, but do you know, ounce I have replaced the combobox with text box, the query works fine. NO EXPLANATION to my knowledge for that. Do you?

    Thanks

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It is difficult to say without seeing the exact code that was executed. But it changing combo to text was the major difference, then perhaps some issue with syntax
    related to combo.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Was the combobox multi-column with lookup alias? Show the combobox properties:

    RowSource
    ColumnCount
    ColumnWidths
    BoundColumn
    ControlSource
    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.

  10. #10
    drhassan is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    10
    Quote Originally Posted by June7 View Post
    Was the combobox multi-column with lookup alias? Show the combobox properties:

    RowSource
    ColumnCount
    ColumnWidths
    BoundColumn
    ControlSource
    Please see the attached images
    Attached Thumbnails Attached Thumbnails combo.png   combo.png  

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I don't see Combo44 referenced in the query parameters.

    Didn't show the BoundColumn property but I will assume it is 1. That means value of the combobox is ID. Is this combobox referenced as parameter under ID field of query? If you aren't filtering by ID then don't include it in the combobox RowSource.
    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.

  12. #12
    drhassan is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    10
    Quote Originally Posted by June7 View Post
    I don't see Combo44 referenced in the query parameters.

    I have two comboboxes in my search form. This is one of them.

    Didn't show the BoundColumn property but I will assume it is 1. That means value of the combobox is ID. Is this combobox referenced as parameter under ID field of query? If you aren't filtering by ID then don't include it in the combobox RowSource.
    I have two comboboxes in my search form. Combo44 is one of them.

    The BoundColumn property apeared in the first image. Anyway, could I change the BoundColumn property to equal 2 to let the combobox filters the values of the second column? This will make it easy for the user to select any value from a list of 20 items.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Try it, what happens?

    Could just uncomplicated the combobox by making it one column since the ID field is not used.
    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.

  14. #14
    drhassan is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    10
    Quote Originally Posted by June7 View Post
    Try it, what happens?

    Could just uncomplicated the combobox by making it one column since the ID field is not used.
    Now, when I entered more than two criteria in my query, the query is coming with no values even with only textboxes in the search form.

    I give up, please could you help me and solve this problem. I have attached my database below.

    Query name: qrysearch
    Form name: searchform

    Thank you in advance
    Attached Files Attached Files

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    There is no query named qrySearch. There is Query QRY_SearchAll with parameters referencing the form. Only 4 of the form controls are referenced. None of the criteria handle nulls as I suggested in post 6. Is it possible that those fields might be null for some records?

    There is only one combobox on the form - Combo44. It has code to requery itself - why?Combo44 still has ID in the RowSource sql and BoundColumn 1.

    I can't read or type the language (Arabic?) so can't test the filtering.
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. SELECT Records based on user input
    By Mattrob in forum Queries
    Replies: 1
    Last Post: 10-10-2013, 12:28 AM
  2. Replies: 3
    Last Post: 03-11-2013, 05:11 PM
  3. Criteria based on user input
    By Alsail77 in forum Queries
    Replies: 5
    Last Post: 08-16-2012, 02:19 PM
  4. Replies: 5
    Last Post: 08-07-2012, 11:14 AM
  5. How do you select a field based on user input?
    By technesurge in forum Queries
    Replies: 5
    Last Post: 06-20-2012, 02:04 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