Results 1 to 10 of 10
  1. #1
    esh112288 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    14

    Query to Pull from Date Range & Check 2 Areas

    I wrote this query to do the following:
    1) Check a date range
    2) Check a Value Stream
    3) Return all records in that date range that have that Value Stream as a Sender or a Receiver



    The following is the SQL and I'm also attaching an image of what the relationships look like. I'm currently getting zero returns from this query and I should get 232. Thanks in advance!

    Code:
    SELECT QA.[QA Number], QA.[QA Type], QA.[QA Trigger], QA.[Discovered On], QA.Shift, QA.[Date Produced], QA.[Lot Number or Cavity Number], QA.[RBS Die Number or Mold Letter], QA.[Details from Issuer], QA.[Immediate Actions Taken], QA.[Scrapped Material?], QA.[Qty and Amt], QA.[Traceability Data], QA.[Is this a HOLD?], QA.[BAM Cut-Off Die Number], QA.[Area Where Discovered], QA.[Receiving Area], QA.[Machine ID], QA.[Product Being Rejected], QA.[Discovered By], QA.[Technician Addressing QA]
    
    
    
    
    FROM (Areas INNER JOIN QA ON Areas.[Area ID] = QA.[Area Where Discovered]) INNER JOIN Areas AS Areas_1 ON QA.[Receiving Area] = Areas_1.[Area ID]
    
    
    
    
    WHERE (
    (
    (QA.[Discovered On]) Between [Forms]![Export QAs to Excel]![Text45] And [Forms]![Export QAs to Excel]![Text47]
    ) 
    AND 
    (
    (Areas.[Value Stream]) Like "*" & Forms![Export QAs to Excel]!VsSelect & "*" OR (Areas_1.[Value Stream]) Like "*" & Forms![Export QAs to Excel]!VsSelect & "*"
    )
    )
    Click image for larger version. 

Name:	Capture.PNG 
Views:	21 
Size:	33.5 KB 
ID:	14371

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Problem is possibly the INNER JOINs. Change to LEFT or RIGHT (not sure which applies here) - 'Show all records from QA and only those ...'
    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
    esh112288 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    14
    Attempted this change and yielded the same result :-(

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is VsSelect a combobox? Is the true value of the combobox the AreaID?
    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.

  5. #5
    esh112288 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    14
    VsSelect is an Unbound combo box where I manually listed values. The values match the 5 existing "Value Streams" that appear in the Areas table.

    Hope this helps.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So it is a ValueList and single column? Sorry, can't see anything wrong with the query. If you want to provide db for analysis, follow instructions at bottom of my post.
    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
    esh112288 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    14
    Database.zip

    I don't see anything wrong with it either. It's making me crazy!

    I'm attaching the database. The Query is called "Export to Excel" and the fields it's referencing are in the form "Export QAs to Excel"

    Let me know if you find anything. I've been staring at it for hours.

    Thanks

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I get records with various combinations of criteria but what parameters should be input in form to return 232 records?

    There are values in Value Stream field not in your combobox list. The combobox list could be created with a query and that will assure all values are available.

    BTW, advise no spaces or special characters/punctuation (underscore is exception) in field and object naming.
    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.

  9. #9
    esh112288 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    14
    Fusion from 10/1/13 to 10/31/13 should return 232 records. The items that do not appear in the combobox list are not areas we're currently using the database for. Can you elaborate more on your idea for the query?

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The combobox RowSource could be: SELECT DISTINCT [Value Stream] FROM Areas ORDER BY [Value Stream];

    I get 223 records. As far as I can recall, I didn't change anything.

    Some records don't have data in these criteria fields. That could cause some records to not retrieve. If that is a concern, need to handle the nulls. One way is to construct fields with an expression and apply the parameters to those constructed fields.

    ValStr: Nz([Value Stream],"")

    You have lookups in tables. I NEVER do that. http://access.mvps.org/access/lookupfields.htm
    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: 04-19-2013, 12:49 PM
  2. Graph date range from query
    By dhicks19 in forum Access
    Replies: 1
    Last Post: 06-29-2012, 12:26 AM
  3. Date Range Query
    By need_help12 in forum Queries
    Replies: 7
    Last Post: 04-25-2012, 01:38 PM
  4. Run query by date range
    By ARickert in forum Access
    Replies: 2
    Last Post: 06-23-2011, 10:54 AM
  5. Complicated Query needs some date range help
    By KevinMCB in forum Queries
    Replies: 1
    Last Post: 01-11-2011, 12:25 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