Proper Syntax for IIf function in Query Criteria

[Ecologist_Guy]

Hi,

I am new to access and I'm having a little trouble using an IIf statement in a query.

I am trying to create a query so users can select a year from a combo box in a form. This was pretty straight forward but I also wanted users to be able to select "All Years". I used a union query to add the term "All Years" to the list of actual years in which data were collected.

Here is what I have so far:
[FrmView_By_Site_Year] = form with the combo box for selecting the year


[cboYear] = combo box on the form ([FrmView_By_Site_Year]) that lets users select a year or the term "All Years".

IIf([Forms]![FrmView_By_Site_Year]![cboYear]="All Years", Like "*",[Forms]![FrmView_By_Site_Year]![cboYear])

The true expressions and the false expressions work fine on their own so I suspect I am using the IIf function incorrectly.

Any help you can offer will be greatly appreciated and used so I can make these data accessible to the general public.

[pbaldy]

I would have the bound column be Null for all years and use this:

http://www.theaccessweb.com/queries/qry0001.htm

[Micron]

The solution proposed may be what you need, but it doesn't educate you as to what the issue is, thus the opportunity to learn something is lost.
What I believe your problem to be is that
first, the LIKE operator can only be used against text. Unless your date values are text, it's not compatible.
second, if you place a date in a query date field and don't include date delimiters, Access will provide them automatically. You can test this in query design. The point being, unless you're passing a date as a date data type, you must delimit your date 03/13/2017 as #03/13/2017#. The only way to do this in conjunction with a Forms! type of reference is to concatenate: "...#" & [Forms]![FrmView_By_Site_Year]![cboYear] & "#..." the ellipses being other portions of the expression as needed.

It's also doable in code by building the WHERE part of a sql statement according to the combo choice.

[Ecologist_Guy]

Thank you both for the help. With your advice, I was able to solve the problem.

I see now that I needed to move the "Like" term in front of the IIf statement. My code now looks like this and runs fine.

Like IIf([Forms]![FrmView_By_Site_Year]![cboYear]="All Years","*",[Forms]![FrmView_By_Site_Year]![cboYear]) Or [Forms]![FrmView_By_Site_Year]![cboYear] Is Null

I should mention two things. First, while pbaldy's solution wasn't exactly what I was looking for, it was still helpful. I added it to my code so now it will show all years if the user selects "All Years" or if they leave the field null. The one problem with pbaldy's solution was once a value is selected it doesn't revert to null unless you clear or close the form. This might throw off some end users if they are performing multiple searches which is why I still wanted to have "All Years" as an option. However, if they are only searching for one set of data they can leave fields blank thanks to pbaldy's suggestion. For anybody reading my original post, everything after and including OR could be left out of the code and my original problem would be solved.

Second, I didn't have problems with the date because the field in the query was already defined as

Years: Year([Date])

where Date is my field that keeps track of when the data were collected.

Thanks again for your help. I am really enjoying using Access and I have just barely scratched the surface of what it can do.

[pbaldy]

I was assuming you had or could use this method to add "all years" as a choice:

http://www.theaccessweb.com/forms/frm0043.htm

That means the user would see "All Years", but the combo would return Null. I'm not personally a fan of using IIf() in the criteria, but that could be my own failing.