Results 1 to 11 of 11
  1. #1
    bds022 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    8

    Iif function to return null or boolean wild card in query criteria

    I am attempting to pass boolean information provided by the user in a check box into the query criteria. If the user has checked the box, I want the query criteria to be true. I seem to have that part of this working. But if the user has not checked a box, I do not want the query criteria to necessarily be false (it could just mean that the user does not care about that criterion). So if the check box is unchecked, I want the query criteria to be empty or wild card, such that it will accept either a true or a false boolean value.



    This is the code that I have so far, entered on the criteria line in the query design view.

    IIf([Forms]![PlanBuilder]![chkActiveSearch],True,Null)

    This is needed because the user has access to several check boxes. I want to return to them data that has the same boxes checked that they have checked in the search criteria AND any other records that might have additional boxes checked. In other words, the data returned need not have an exact match on all the empty check boxes in the user search criteria.

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Seems like that should work. For the each checbox field after the first one in the query, put that IIf code (with the different check box field) in the Or box on the next line.

  3. #3
    bds022 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    8
    Thank you, but NULL seems to be the problem. That is not producing the behavior I would like. Is there some other way to say "any" or "true or false"?

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Might not understand what you are trying to do. So a check box has to be true for it to return the record right? So if they check at least 1 box you want it to return that record? So only way it does not return a record is if all of the check boxes were not marked? Maybe can you give an example of some data records with the check boxes and what you want the output to be.

  5. #5
    bds022 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    8
    Thanks for your help. Actually if none of the check boxes are marked then all the data should be returned. The list should then be refined as additional boxes are checked. I am dealing with a list of courses in an academic program. Some courses are required of multiple majors. The user can refine the list by selecting one or more majors. When a major is selected, all courses in that major should be returned, and I don't want it to skip courses that happen to also be part of another major (just because that other major check box is not marked).

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So you let them select 1 or more majors and when they do, you display all the courses for those majors and let them select a check box by each major to say if they want to take it or not? Sorry this might be really simple what you are needing and I'm just missing it. Do you have any screen shots of the query or screens or any test data.

  7. #7
    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 agree with Bulzie that your requirement is unclear. Can you step back from Access and describe in a few lines of simple English WHAT exactly is the process in overview. Let's start at the 30,000 ft level and work down to a little more detail.

    I recognize you are dealing with
    courses
    academic program
    majors
    ...


    and these would be "entities/proposed tables" in a model of the database. You haven't mentioned these in detail nor what relates them.
    Perhaps a few examples would help readers visualize your set up.

    Good luck.

  8. #8
    bds022 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    8
    I actually was able to solve it this way. Each one either evaluates to (True or True) if the check box is checked or (False or True) if not checked, therefore accepting any input.



    I'm now stuck trying to integrate combo boxes in with this (another search item is an input for course number so that one is not boolean. Again, I don't want a blank combo box to mean that a blank course number needs to be found, but instead that course number should not be a query criterion. Thanks; really appreciate the engagement on this.

  9. #9
    bds022 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    8
    I actually was able to solve it this way. Each one either evaluates to (True or True) if the check box is checked or (False or True) if not checked, therefore accepting any input.

    Click image for larger version. 

Name:	screenshot.PNG 
Views:	15 
Size:	7.5 KB 
ID:	40741
    I'm now stuck trying to integrate combo boxes in with this (another search item is an input for course number so that one is not boolean. Again, I don't want a blank combo box to mean that a blank course number needs to be found, but instead that course number should not be a query criterion.

    The individual components of this code do exactly what I want.

    IIf(IsNull([Forms]![PlanBuilder]![cmbABETOutcomeSearch]),Like "#" Or Is Null,[Forms]![PlanBuilder]![cmbABETOutcomeSearch])

    For example
    Like "#" Or Is Null
    returns all outcome numbers, and any that are blanks, when entered in directly. But I get a syntax error when I put it within iff.

    Thanks; really appreciate the engagement on this.

  10. #10
    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
    Post edited since OP posted a solution and has resolved the issue.

    You may wish to research cascading combo boxes for additional info.

    Good luck

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397

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

Similar Threads

  1. wild card * or % which one to use
    By ntambomvu in forum Access
    Replies: 4
    Last Post: 12-02-2018, 06:27 PM
  2. Replies: 3
    Last Post: 01-23-2018, 03:44 PM
  3. drill down and wild card criteria
    By Jen0dorf in forum Access
    Replies: 3
    Last Post: 02-12-2016, 12:12 PM
  4. QUERY: Date/Time with a wild card as criteria?
    By excellenthelp in forum Access
    Replies: 3
    Last Post: 04-21-2015, 03:06 PM
  5. Criteria from form to query with wild card
    By SteveW in forum Queries
    Replies: 3
    Last Post: 04-01-2011, 07:49 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