Results 1 to 10 of 10

Query Criteria in a Form Either or, or Both

  1. #1
    MichaelA is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    92

    Query Criteria in a Form Either or, or Both


    I have a form that sets query criteria then runs the query. The query is searching a gradebook for students who are missing assignments. The form asks for Student Name and Course.


    1. If I select the student, I would like it to return a list of all missing assignments for all courses for just the selected student.
    2. If I select Student and Course, I would like it to return a list of only those missing assignments for the selected course for the selected student.
    3. If I select just the course, I would like it to return the missing assignments for all students for the selected course.


    I have been successful in setting the query criteria and getting each of the above to work by itself but I can't seem to get all three options together.

    Thank you in advance!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,348
    Post the attempted query SQL. Is it a dynamic parameterized query? Are you filtering on numeric ID fields? Are you using LIKE and wildcard? Review http://allenbrowne.com/ser-62.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.

  3. #3
    MichaelA is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    92
    SELECT QryAssignmentStudentMatch.StudentID_PK, [FirstName] & " " & [LastName] AS [Full Name], tblGrades.Grade, QryAssignmentStudentMatch.Course, QryAssignmentStudentMatch.AssignID, QryAssignmentStudentMatch.Assignments
    FROM tblGrades RIGHT JOIN QryAssignmentStudentMatch ON (tblGrades.[AssignmentID_FK] = QryAssignmentStudentMatch.AssignID) AND (tblGrades.[StudentID_FK] = QryAssignmentStudentMatch.StudentID_PK)
    WHERE ((([FirstName] & " " & [LastName])=[Forms]![frmMissingAssignments]![cboFullName]) AND ((tblGrades.Grade) Is Null) AND ((QryAssignmentStudentMatch.Course)=[Forms]![frmMissingAssignments]![cboCourse]));



    Quote Originally Posted by June7 View Post
    Post the attempted query SQL. Is it a dynamic parameterized query? Are you filtering on numeric ID fields? Are you using LIKE and wildcard? Review http://allenbrowne.com/ser-62.html

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,348
    I don't use dynamic parameterized queries. I prefer VBA to build filter criteria.

    Did you review the referenced link? It has example of both.
    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
    MichaelA is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    92
    Yes, I received the link. I'll try to make sense of it.

    Quote Originally Posted by June7 View Post
    I don't use dynamic parameterized queries. I prefer VBA to build filter criteria.

    Did you review the referenced link? It has example of both.

  6. #6
    MichaelA is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    92
    That's more than a little bit beyond me.


    Quote Originally Posted by June7 View Post
    I don't use dynamic parameterized queries. I prefer VBA to build filter criteria.

    Did you review the referenced link? It has example of both.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,348
    What exactly do you not follow - the VBA or the dynamic query?

    In your case, dynamic parameter query WHERE clause would be:

    WHERE ([Forms]![frmMissingAssignments]![cboFullName] Is Null OR [FirstName] & " " & [LastName] Like "*" & [Forms]![frmMissingAssignments]![cboFullName] & "*")
    AND ([Forms]![frmMissingAssignments]![cboCourse] Is Null OR QryAssignmentStudentMatch.Course Like "*" & [Forms]![frmMissingAssignments]![cboCourse] & "*")
    AND tblGrades.Grade Is Null
    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.

  8. #8
    MichaelA is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    92
    It's all Greek to me.

    Quote Originally Posted by June7 View Post
    What exactly do you not follow - the VBA or the dynamic query?

    In your case, dynamic parameter query WHERE clause would be:

    WHERE ([Forms]![frmMissingAssignments]![cboFullName] Is Null OR [FirstName] & " " & [LastName] Like "*" & [Forms]![frmMissingAssignments]![cboFullName] & "*")
    AND ([Forms]![frmMissingAssignments]![cboCourse] Is Null OR QryAssignmentStudentMatch.Course Like "*" & [Forms]![frmMissingAssignments]![cboCourse] & "*")
    AND tblGrades.Grade Is Null

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,348
    Did you even try replacing the WHERE clause?

    Open your query in SQLView and copy/paste the code I posted.

    Then switch to DesignView to see structure there.

    The VBA example seems rather straightforward. It is a commonly referenced tutorial. Even provides a sample database you can download and explore to learn with. Will certainly take some time and effort but should be worthwhile.
    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
    MichaelA is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    92
    Sorry, I was gone most of the afternoon and evening. I copied and pasted the code. It worked great. I'll look through that website you posted later. I'm thinking I need to back way up and take some kind of course from the beginning.

    Thank you, very much.


    Quote Originally Posted by June7 View Post
    Did you even try replacing the WHERE clause?

    Open your query in SQLView and copy/paste the code I posted.

    Then switch to DesignView to see structure there.

    The VBA example seems rather straightforward. It is a commonly referenced tutorial. Even provides a sample database you can download and explore to learn with. Will certainly take some time and effort but should be worthwhile.

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

Similar Threads

  1. Query Criteria from form
    By Brightspark98 in forum Queries
    Replies: 2
    Last Post: 01-16-2017, 04:35 PM
  2. Replies: 1
    Last Post: 11-13-2014, 11:34 PM
  3. Replies: 1
    Last Post: 03-28-2013, 07:54 AM
  4. Replies: 5
    Last Post: 09-20-2012, 03:27 PM
  5. Query criteria in a form
    By sefiroths in forum Queries
    Replies: 1
    Last Post: 12-23-2009, 05:15 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
  •  
Tech Forums: Microsoft Office Forums