Iif statement with multiple if true/if false actions

[smaier]

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?

[Micron]

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.

[ranman256]

make a table, then use a combo box for user to pick 1 item, and query shows the result. no code.

[smaier]

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?

[ranman256]

IIF statement only has 2 results: condition, TRUE, FALSE.
your example had 5

[smaier]

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!

[smaier]

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!

[Micron]

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.