Results 1 to 15 of 15
  1. #1
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98

    Query not showing ALL results

    Greetings all,

    I have a query that seems to have crapped itself.
    Short description: I run my query and so a search for an item, the results show only 4 of the items that match the search criteria that I typed in.


    This seems to happen after I enter new data into the table. For some reason my query will not pull all of the results that match the search string for some reason.

    I have not modified anything other than entering new data into the table.
    So for example: If I'm doing a search for AG Cables, it only shows me 4 results instead of the 30+ that are actually in the table.

    This ring a bell to anyone?
    I'm half tempted to just rewrite the query, but I hate having to do that, plus I shouldn't have to do that.

    Regards.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It would help readers if you showed
    -your query SQL,
    -some sample data
    -a copy of the database.

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Is the query open when you import the new data?
    If so, you may need to close it and re-open it (AFTER the new data is imported) in orider to see the new data.

  4. #4
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    The query is not open when I enter the new data. I use my Front End GUI I designed for the data entry.

    I'm attaching some screenshots.
    #1 The search Form used
    #2 The results from the query search from #1
    #3 The Query I'm using with search criteria
    #4 The table showing items that should be displayed in the query search results

    I can upload the DB if needed, I'll just have to strip out a few things first.

    Click image for larger version. 

Name:	Search_001.png 
Views:	24 
Size:	9.1 KB 
ID:	38627Click image for larger version. 

Name:	Search_002.png 
Views:	23 
Size:	33.8 KB 
ID:	38628Click image for larger version. 

Name:	Search_003.png 
Views:	23 
Size:	50.6 KB 
ID:	38629Click image for larger version. 

Name:	Search_004.png 
Views:	23 
Size:	115.2 KB 
ID:	38630

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I see you are using a Search Form too in your query. You many need to make sure that is closed while new data is imported, so as to Refresh everything.

  6. #6
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Quote Originally Posted by JoeM View Post
    I see you are using a Search Form too in your query. You many need to make sure that is closed while new data is imported, so as to Refresh everything.
    Yes, I did that. The only thing open was the main menu and the new data entry form when entering the new items in the DB.

    I've even gone as far as re-linking the FE to the BE (it's a split DB), and restarting Access.
    Same results.

  7. #7
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    I think I may have found the problem... not the solution, just the problem.

    So, going back to my Query, the search criteria for Description seems to work perfectly fine... by itself.
    As soon as I add additional search criteria, such as the SerialNumber or PartNumber, that's when my results start to flake out.

    So, now I'm curious why this would be happening and how many of my other queries are probably doing the same thing I just haven't checked yet...

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    So, going back to my Query, the search criteria for Description seems to work perfectly fine... by itself.
    As soon as I add additional search criteria, such as the SerialNumber or PartNumber, that's when my results start to flake out.
    It is important to note how multiple criteria works in the Query Builder.
    If you enter multiple criteria on the same Criteria line, they will be treated as AND conditions (meaning ALL of the criteria must be met in order to return a record).
    If you enter the different crtieria statements on different Criteria line, they will be treated as OR conditions (meaning only one of the criteria must be met in order to return a record).
    Note that this becomes more evident if you switch the query to SQL View.

    Does that explain what you are seeing?

    If it does not, looking at example may be the best way to approach this.
    1. Provide your Search Criteria selections
    2. Tell us a record that is NOT being returned, but should be, and tell us its value of all the fields used in the Search Criteria.

  9. #9
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Quote Originally Posted by JoeM View Post
    It is important to note how multiple criteria works in the Query Builder.
    If you enter multiple criteria on the same Criteria line, they will be treated as AND conditions (meaning ALL of the criteria must be met in order to return a record).
    If you enter the different crtieria statements on different Criteria line, they will be treated as OR conditions (meaning only one of the criteria must be met in order to return a record).
    Note that this becomes more evident if you switch the query to SQL View.

    Does that explain what you are seeing?

    If it does not, looking at example may be the best way to approach this.
    1. Provide your Search Criteria selections
    2. Tell us a record that is NOT being returned, but should be, and tell us its value of all the fields used in the Search Criteria.
    Ok, that makes sense. I should have realized that earlier.
    However, I tried putting the other 2 search strings in the OR lines and I now get everything I search for PLUS other things that should show... it basically shows me everything in that category now and doesn't match the search string "AG" as per the example...

    Still testing things...

  10. #10
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    OK. As I said, if you cannot figure it out, please post those details I asked for in the prior email.

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    FriQenstein,

    For clarity and in plain English, what exactly do you want returned by your Search?
    Are you trying to find records some field or fields containing a partial string? Please be clear.

    Some people want all records with this string anywhere in any field. Some want exact matches in a specific field.
    I'm not trying to be picky -- just want you to tell us what the requirement is.
    Good luck.

  12. #12
    Join Date
    Apr 2017
    Posts
    1,673
    For setting query conditons, along wit AND/OR, parenthesis must also used often. Some examples of conditons:
    Code:
    ...WHERE field1 = Value1 AND field2 = Value2 (returns all records where both conditions are TRUE)
    ...WHERE field1 = Value1 OR field2 = Value2 (returns all records where at least one condition is TRUE)
    ...WHERE field1 = Value1 OR field1 = Value2 (returns all records where either of conditions is TRUE)
    ...WHERE field1 IN (Value1,Value2) (a shorter version of previous example)
    ...WHERE field1 = Value1 AND field1 = Value2 (returns always nothing)
    ...WHERE field1 = Value1 OR (field1 = Value2 AND Field2) = Value3 (returns all recrods where 1st condition for Field1 is TRUE without any limitaions for Field2 - or both 2nd condition for Field1 and condition for Field2 are true)
    e.t.c.

  13. #13
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Ok, let me see if I can lay this out correctly.

    I'm using a simple Form to search for items. The search string I'm searching for is "AG"... as indicated by the initial screenshots.

    The Query criteria is as follows:
    -- In the Criteria row: Like "*" & [Forms]![Bolt-Parts_SearchF]![DescriptionBox] & "*"
    -- In the Or row: Like "*" & [Forms]![Bolt-Parts_SearchF]![SerialNumberBox] & "*"

    Ok, now having said that, below are some screenshots showing what's being used and the results that appear after the Query is run and dumped out to the Report.

    As you can see in the Report, the results show items that do not match the search criteria. I'm trying to get it to show only the items that have "AG" in the description, like the AG Cable, AG Jumper Cable, AG Splitter Cable, etc.
    However, I'm getting other items listed such as the G-Gun, Bird Push Pole, Air Calibration Adaptor, etc.

    And to recap a bit, if I ONLY use the first string in the criteria, and delete the OR criteria line, my output is perfect. It only shows what I'm actually looking for. But as soon as I add the OR statement, I basically get the entire inventory listed in the output from the Query.

    Hope that clarifies is a bit.

    Regards.
    Attached Thumbnails Attached Thumbnails Search_005.png   Search_006.png   Search_001.png  

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I suggest you provide a copy of the database as you indicated in post #4
    I can upload the DB if needed, I'll just have to strip out a few things first.
    Make a zip include FE and BE.
    Good luck.

  15. #15
    Join Date
    Apr 2017
    Posts
    1,673
    What do you get, when you have non-empty criterions for all 3 (2) fields?

    I got the impression, that currently you a searching for rows where criterion is like "AG" or serial number has any value! (".. Like *" & "" & "*")

    Cases like this are reason, I prefer to generate the WHERE clause in such situations always through code. I.e. Step1 - when 1st field is not empy, then the string for where clause is something. Step2 - when the string is empty and 2nd field is not empty then the string is something, otherwise something (else) is added to string. Etc.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-15-2017, 11:09 AM
  2. Replies: 7
    Last Post: 05-27-2015, 12:45 PM
  3. Not all results showing in query
    By lewis1682 in forum Queries
    Replies: 3
    Last Post: 09-08-2013, 03:16 PM
  4. Query results showing table relationship
    By Steven.Allman in forum Queries
    Replies: 2
    Last Post: 04-23-2012, 01:31 PM
  5. Query showing unexpected results
    By johnmerlino in forum Queries
    Replies: 30
    Last Post: 10-25-2010, 07:08 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