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

    Problems with Query and blank fields

    Greetings all,



    Let's say I have a query that basically pulls ALL information from tblClientInventory.
    Now, in the Criteria field under company name, I put in ="PGS" so that it ONLY pulls all records for that company. All is fine up to this point.
    Click image for larger version. 

Name:	Query Blank Error-1.PNG 
Views:	15 
Size:	37.8 KB 
ID:	42899
    ... and the results from the query ...
    Click image for larger version. 

Name:	Query Blank Error-2.PNG 
Views:	15 
Size:	100.6 KB 
ID:	42900

    Now, I add tblOutInvoicesC to the query and the query now ONLY pulls records that actually have something in the invoice fields in the tables. Even though I never specified any criteria for the invoice in the query criteria fields AND I still have the company name in the criteria. Even when I remove the company name criteria, the query still ONLY pulls records that have fields with data in the invoice table.
    Click image for larger version. 

Name:	Query Blank Error-3.PNG 
Views:	15 
Size:	44.0 KB 
ID:	42901

    ... and the results from the query ...
    Click image for larger version. 

Name:	Query Blank Error-4.PNG 
Views:	15 
Size:	13.3 KB 
ID:	42902

    This also has a problem when there is no entry for the invoice number in the table related to the item in tblInventory... the query basically returns nothing.. a completely empty results page simply because none of the invoices numbers have been populated to the table yet. Shouldn't it just return everything, including blank fields like a normal good boy query?

    My question is, why? Just because there are blank fields in a table is no reason to simply nuke all of my records during my search.

    Here is an alternate example... in the exact same scenario, I have a field for serial numbers. If a unit does not actually have a serial number, it is no big deal; the query still pulls the record and displays it without any issues. However, as soon as I try to add the invoice table to the query, my data disappears because of empty fields in the invoice number table.

    This does not make sense to me. It should operate the same as it does with the missing serial number field data.

    I would appreciate any insight anyone has to shed on this. It is probably something simple that I am missing, but I cannot quite figure it out.

    Regards.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Is query using INNER JOIN? Try LEFT or RIGHT.
    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.

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I'm not sure about this but I think your problem has something to do with the fact that you have two relationships from tblClientC.
    I would try removing each, in turn and see if having just one of them gives the desired result
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Sorry June7. I missed your reply.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    No apology needed. Actually a very good point. May be a compound issue.
    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
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    @June7 & @Bob Fitz,

    You were both correct, actually.
    The default JOIN I never messed with, but apparently it wasn't the correct one. I believe that secondary relationship link I had was causing it to go crazy.

    I removed the extra relationship link, and changed the JOIN to match the tblClientInventory so it pulls ALL from that table and only matching ones from the tblOutInvoices table.

    I am still messing around with it, so I will need to verify this is fixed and see how it has affected my overall DB build... something tells me I will have to redesign my invoice search forms/queries now that the JOIN has been changed.

    Thanks for the help, both of you. STARRED.

    I will post back with the final conclusion prior to marking this as resolved.

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    If you feel that my thoughts have helped then I'm glad

    BTW I don't think that changing the relationship in this query will have an effect anywhere else in your app unless you're using it as a data source for other forms/reports but if that is the case, then it should be simple enough to save this modified query as a separate query and leave the original for the other forms/reports
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by June7 View Post
    No apology needed. Actually a very good point. May be a compound issue.
    Thank you June. I'm always wary of offending people but particularly those that have more experience and knowledge than myself. After all, you never know when you may need their help
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  9. #9
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Ok.
    Everything appears to be in order now. I will close this thread out.

    Thanks again.

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

Similar Threads

  1. Replies: 15
    Last Post: 08-11-2018, 02:02 PM
  2. Replies: 1
    Last Post: 01-31-2018, 04:06 PM
  3. Replies: 1
    Last Post: 02-24-2017, 12:53 PM
  4. Query is excluding records with blank fields
    By Menelaus in forum Queries
    Replies: 1
    Last Post: 03-28-2013, 08:14 PM
  5. searching for blank fields with a query
    By ironman in forum Queries
    Replies: 0
    Last Post: 03-04-2011, 03:48 PM

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