Results 1 to 3 of 3
  1. #1
    Simba1977 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    4

    Help with Query - Seems to only pull one column criteria and I need 3 columns criteria

    Good afternoon,

    I have a table that I am running a query on. in the query Design across the different columns I have different "filters" I am adding.... However it seems like Access is only running one "filter"....I copied my SQL code below. In my results I am not seeing the Office Name being filtered out nor the Enrollment end date only showing blank values. But it is filtering out the Programs to only show the correct programs..... What am I doing wrong?

    SELECT [Case Management Report].[Office Name], [Case Management Report].[Program Enrollment TP Last Updated By], [Case Management Report].[Program Enrollment TP Recorded on Behalf Of], [Case Management Report].[Unique Program Enrollment ID], [Case Management Report].[Case Number], [Case Management Report].[Seeker Name], [Case Management Report].[Program of Enrollment], [Case Management Report].[Enrollment Start Date], [Case Management Report].[Enrollment End Date], [Case Management Report].[Last Completed Staff assisted service PE], [Case Management Report].[Last Completed Staff Assisted Date PE], [Case Management Report].[Last Completed Service Linked to this Program Enrollment], [Case Management Report].[Days Since Last Completed Service Linked to this Program Enrollm], [Case Management Report].[Most Recent Active Duration Service Start Date Linked to this Pr], [Case Management Report].[Most recent Active Duration Service Linked to this Program Enrol], [Case Management Report].[Last Completed Service Date linked to Any Program Enrollment], [Case Management Report].[Last Completed Service linked to Any Program Enrollment], [Case Management Report].[Days Since Last Completed Service linked to any Program Enrollme], [Case Management Report].[Most Recent Active Duration Service Start Date linked to any Pro], [Case Management Report].[Most Recent Active Duration Service linked to any Program Enroll], [Case Management Report].ID, [Case Management Report].*
    FROM [Case Management Report]
    WHERE ((([Case Management Report].[Office Name])="San Juan Career Center") AND (([Case Management Report].[Program of Enrollment])="WIOA Adult") AND (([Case Management Report].[Enrollment End Date]) Is Null)) OR ((([Case Management Report].[Program of Enrollment])="WIOA Out of School Youth")) OR ((([Case Management Report].[Program of Enrollment])="WIOA Dislocated Worker")) OR ((([Case Management Report].[Program of Enrollment])="WIOA In School Youth")) OR ((([Case Management Report].[Program of Enrollment])="WIA Out of School Youth"))


    ORDER BY [Case Management Report].[Days Since Last Completed Service Linked to this Program Enrollm] DESC;

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I suspect you have the criteria for the office name and enrollment end date on the first row (in query design) but the programs going down on multiple rows. You need to add the office name and enrollment end date to all the rows that have a program name or better look into keeping it all in one row and use IN("WIOA Adult","WIOA Out of School Youth",....) for the program.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It is often easier to write this sort of criteria in sql - you can also leave out the report name since there is only the one query/table. And only include brackets when required

    Code:
    WHERE ([Office Name]="San Juan Career Center" AND [Program of Enrollment]="WIOA Adult" AND [Enrollment End Date] Is Null)
     OR ([Program of Enrollment]="WIOA Out of School Youth") 
    OR ([Program of Enrollment]="WIOA Dislocated Worker")
     OR ([Program of Enrollment]="WIOA In School Youth")
     OR ([Program of Enrollment]="WIA Out of School Youth")
    I suspect this is not what you mean't.

    Once you have it right, save the query. When you reopen it in design view - you will see how access reinterprets it for the query grid

    As a general comment, it is not a good idea to include spaces in table and field names - it can lead to problems and requires additional typing (you also need the square brackets)

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

Similar Threads

  1. Replies: 2
    Last Post: 07-08-2020, 01:20 PM
  2. Replies: 2
    Last Post: 07-03-2017, 09:10 AM
  3. Replies: 4
    Last Post: 06-09-2017, 01:44 PM
  4. Replies: 2
    Last Post: 04-02-2015, 12:45 PM
  5. Replies: 3
    Last Post: 06-21-2013, 09:06 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