Query criteria using IIF when false

[james28]

Hello all!




I am very new to Access and have managed to run into a road block of sorts. I have a very simple query created that pulls from a table in an access 2010 database. I then have an equally simple form named "testform" that has a single combobox named "ComboAnimalType" and a run query button. The combobox has 3 choices, which were manually entered. Dog, Cat and All. In my table, I have a field called Animal, which lists 10 different types of animals, 2 of which are Dog and Cat.


For now, I want "Cat" and "All" to be treated the same way. If either of the options are selected, I want it to be as if there is nothing in the query's criteria for that field. Put another way, I want it to display everything.

When I select "Dog", I want the query to be limited based on the criteria that animal = Dog.


Using an IIF expression, I am able to accomplish the Dog portion. The problem that I am running in to is that I can not seem to figure out how to make the false portion of the IIF display all. I have tried the following, all of which work when the combobox is set to Dog, but return nothing when the combobox is set to Cat or All.

IIf([forms]![testform]![ComboAnimalType]="Dog","Dog","*")


IIf([forms]![testform]![ComboAnimalType]="Dog","Dog",)


IIf([forms]![testform]![ComboAnimalType]="Dog","Dog","")


IIf([forms]![testform]![ComboAnimalType]="Dog","Dog",<>null)


IIf([forms]![testform]![ComboAnimalType]="Dog","Dog",<>"null")


IIf([forms]![testform]![ComboAnimalType]="Dog","Dog",Like "*")


IIf([forms]![testform]![ComboAnimalType]="Dog","Dog",*)



Just to check to see if I had the IIF completely wrong, I changed it to
IIf([forms]![testform]![ComboAnimalType]="Dog","Dog","Cat")

This works just fine to limit the query based on animal being Cat, if anything other than Dog is selected in my combobox, so I know that the expression is in the right order, I am just puzzled as to what I need to use in order to show all results when false.



If you could provide any assistance, I would greatly appreciate it.


Thank you!
James

[June7]

Try:

WHERE [Type] LIKE IIf([forms]![testform]![ComboAnimalType]="Dog", "Dog", "*")

But why not:

WHERE [Type] LIKE IIf([forms]![testform]![ComboAnimalType]="All", "*", [forms]![testform]![ComboAnimalType])

[james28]

June,

Thank you for the prompt reply. I really appreciate it.


Using the value of the combo box was my end goal, I was just trying to simplify as much as possible, to focus on the part that is giving me trouble.


I was not able to get your example to work, but I think that is because I do not fully understand it.


I am currently working in Design view on the query. I had exactly what I had pasted in my original reply in the Criteria section of the Animal column of my query.

When I tried the same thing with your example, leaving out the WHERE [Type] LIKE, it does not work.

I then tried to put your exact line in the criteria space in design view and after I saved and closed the query, when I opened it again, it created a new column called "WHERE [Type]"

The "WHERE [Type]" column was unchecked and in is criteria section it had Like IIf([forms]![testform]![ComboAnimalType]="Dog","Dog","*")

When I used the form to generate the query, selecting Dog returned 0 results (when it should have returned 2). Selecting anything other than Dog, like Cat or All, returned all of the results as it should have.


If I did something wrong, could you please point me in the right direction?

Thank you again!

[June7]

Is the combobox a lookup? Is it multi-column? Is the actual value of the combobox the text descriptor or is it a number ID? Is the Type field a number or text type?

[james28]

June,

Thank you again for the quick reply. I will try to answer as best I can.

Is the combobox a lookup? No, I do not think so. I am entering the values manually.

Is it multi-column? No, I do not think so. There is only 1 answer showing in the combobox at a time.

Is the actul value of the combobox the text descriptor or is it a number ID? I believe that it is the actual text, not a number ID

Is the Type field a number or a text type? That depends, I have no "type" field. If you mean the "Animal" from my table, the field that contains the type of animal, then yes. I have that set to Text.

I have attached a copy of what I am working with. That may make it easier.

Database114.accdb


Thank you again for the help!
James

[June7]

I was only guessing when I used field name Type. Use your actual field name, which I see now is Animal and when I re-read your original post, I see it there as well. I had missed that detail.

[james28]

June,

Blah! I should have realized that. I was wondering what it meant, but it sounded official.


I updated my query to include Animal instead of Type. I deleted the criteria completely, so that I could make sure not to miss anything. When I did so, I did the same behavior of creating a new column. This time, the column was named "WHERE [Animal]".

After saving and closing the query, I went back to the form to run it and it still works for "Cat" and "All", but returns 0 results for "Dog".



Is there anything that I am missing?


Thank you!

[June7]

Open the query in design view and put the criteria under the Animal field but don't include "WHERE Animal", just the LIKE IIf expression.

What I originally posted was the syntax as viewed in the SQL window.

[james28]

June,

Thank you, thank you, thank you!

That seems to have done it!

I guess I was very close before, I was just missing the Like in the front. If you do not mind my asking, why is Like required before the IIF? IIF by itself allows the true condition to work, but not the false, which seems strange.


Thank you again!
James

[June7]

Without LIKE, the query will assume = operator. LIKE is needed with wildcard.

[james28]

June,


That makes sense. Thank you again for taking the time to help. I have marked the thread as solved.

-James