Results 1 to 8 of 8
  1. #1
    smaier is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2020
    Posts
    43

    Iif statement with multiple if true/if false actions

    Hello, all!

    Admittedly, queries really aren't my strong suit but I am wondering if there's a way to have multiple if true and/or if false actions within an Iif statement.

    Example in query criteria (gets a "too complex" error when run):
    Iif([Forms]![ToolFinderForm]![FilterByType1Checkbox]=-1,"Philips" Or "Flat Head","Hex" Or "Torx")

    I have been digging a bit and mostly what I found were dealing with the expression/condition part of the statement. If I just put the if true part by itself in the query criteria I get query returns for both Philips and Flat Head.



    If I only use a single criteria as below, it works as I expect it to:
    Iif([Forms]![ToolFinderForm]![FilterByType1Checkbox]=-1,"Philips","Hex")

    Is it possible to have multiple criteria in the if true and/or if false parts of an Iif statement?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737

    clarification

    I tend to like to use analogies to drive home a point because I believe that then you will never forget as opposed to me just telling you what to do.

    If I said to you "Would you like a peanut butter cookie?" you would understand. If I said "Would you like a cookie or a banana?" you would still understand because you use logic. Computers cannot. What you have (in the OR parts) is equivalent to me walking up to you and saying "Or a banana?".

    If that doesn't help I can be less cryptic.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a table, then use a combo box for user to pick 1 item, and query shows the result. no code.

  4. #4
    smaier is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2020
    Posts
    43
    Hello, Micron!

    I'd be a fibbing if I said I understood the reason why it won't work at the fundamental level, but there have been times with Access and Excel where I just chalked it up to "welp, I guess that's just the way it works", which is just another way of saying I don't understand it well enough. Having come into Access sideways (rather than from the bottom up) I am at the mercy of those granular things I didn't pick up along the way.

    As it stands right now I am still wrestling with my internal argument of "why would just entering the multiple if true portion of the statement into the query criteria work, yet not work within the iif statement?!?". My fallback solution I had before I posted was to just create a separate query for the if true and if false portions of the statement and have the checkboxes change the row source property of the listbox. Wouldn't be the most graceful solution and not something I'd be bragging about to anyone who knew what they were doing, but it'd work.

    So, I kind of get what you're saying. It just needs to sink in a bit more. I just need to stop looking at the if true or if false parts of the iif statement being the equivalent of me just typing that into the query criteria. If that makes any sense?

  5. #5
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    IIF statement only has 2 results: condition, TRUE, FALSE.
    your example had 5

  6. #6
    smaier is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2020
    Posts
    43
    Quote Originally Posted by ranman256 View Post
    make a table, then use a combo box for user to pick 1 item, and query shows the result. no code.
    Hello, ranman!

    That never occurred to me. I was thinking of creating separate queries for the if true and if false parts of the iif statement and using an After Update event to run some VBA to change the row source property of the (outputting the query results) listbox and then requery'ing. Your idea seems a bit cleaner.

    Cheers!

  7. #7
    smaier is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2020
    Posts
    43
    Quote Originally Posted by ranman256 View Post
    IIF statement only has 2 results: condition, TRUE, FALSE.
    your example had 5
    Right, my error was just assuming that "'Philips' Or 'Flat Head'" in the iif statement was an output that would directly translate into what would be plugged into the query criteria. Bad understanding of how the statement works on my part.

    Thank you again!

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Ranman's advice is good and to be honest I didn't look closely enough at what you had written. I focused on a format that should have been
    Iif([Forms]![ToolFinderForm]![FilterByType1Checkbox]=-1 OR [Forms]![ToolFinderForm]![FilterByType1Checkbox]= X OR [Forms]![ToolFinderForm]![FilterByType1Checkbox]= Y...)
    but I see that you're not really doing that. Also in the case of IIF, each portion is separated by ' , ' so yeah, you had too many.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 2
    Last Post: 10-20-2015, 02:01 PM
  2. Replies: 8
    Last Post: 10-06-2014, 03:02 PM
  3. Replies: 5
    Last Post: 07-15-2014, 12:00 PM
  4. Replies: 2
    Last Post: 10-29-2012, 11:28 AM
  5. Replies: 7
    Last Post: 01-11-2012, 12:24 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