IFF and LIKE in an Access Query to Filter Results

[Steve R.]

My understanding of the LIKE operator seems to be deficient for filtering a query when using the Access query pane. I have a combo box that lists the values to be filtered. One option in the combo box is "No Selection". If "No Selection" is selected, I want the filter turned off, in other words ALL records are shown.

In theory the following code would seem to be correct when placed in the criteria line. It does not work:

IIf([forms]![type5frm].[combo2]=24,Like "*",[forms]![type5frm].[combo2])

If the value of COMBO2 =24 (No Selection), then I would like all records to show up, but no records are displayed. The FALSE side of the IIF statement works correctly.

As an experiment I tried LIKE by itself and it displayed all the records:

 LIKE "*"

Another failed try:

IIf([forms]![type5frm].[combo2]=24,[agency]) Like "*",[forms]![type5frm].[combo2])

AGENCY is the fieldname.

A more exotic attempt that failed:

IIf([forms]![type5frm].[combo2]=24,Val(Str([agency]) Like "*"),[forms]![type5frm].[combo2])

Any thoughts?

[KenHigg]

How are you populating the combo box rowsource?

[Steve R.]

SELECT [agencylist].[agencyidnum], [agencylist].[agency] FROM agencylist ORDER BY [agency];

AGENCYIDNUM is numeric, the value "24" corresponds to "No Selection". AGENCY is the name corresponding to AGENCYIDNUM.

[KenHigg]

My first suggestion is to build the sql string with code...

[Steve R.]

That's what I did with my other database. . I just thought this approach might be easier. Guess not.

With my other database, I have five combo boxes, as you click on each combo box the filter becomes more restrictive so you can reduce the number of records found to a manageable level. If you choose "No Selection" for a combo box those records are selectively added back to the list. The database I am currently working on will probably have the same number of combo boxes, so I can import the code.

The two databases track building permits, but one is now an "archive" database as it was done in Dbase. The person who developed left so it became orphaned and unused. Several years later, I got hired so I started a new version with Access and I am doing cosmetic fixes on the old database.

Thanks for the help. I guess this is one of Access' undocumented features.

[Jon K]

SELECT [agencylist].[agencyidnum], [agencylist].[agency] FROM agencylist ORDER BY [agency];

AGENCYIDNUM is numeric, the value "24" corresponds to "No Selection". AGENCY is the name corresponding to AGENCYIDNUM.

We normally put only one field in a combo box that is used as query criteria because a query can recognize only the value returned by the Bound Column of the combo box.

And if either the true-part or the false-part of the IIF criteria doesn't use the = operator, we can't put the IIF expression in the Criteria row in query Design View.


Assuming agencylist contains the record: 24, No Selection, you can change the bound column of the combo box to 2 and set the criteria for the field in a new column in the query grid like the following:-
----------------------------------
Field: IIf([forms]![type5frm].[combo2]="No Selection", True, [Agency]=[forms]![type5frm].[combo2])

Show: uncheck

Criteria: True
----------------------------------

See this thread for explanations:
http://www.access-programmers.co.uk/...d.php?t=103312
.

[Steve R.]

Using "TRUE" did not work Jon. But there is a solution that you helped develop back in 2002!!

Like (IIf(([forms]![type5frm].[combo2])=24,"*",[forms]![type5frm].[combo2]))

This solution is courtesy of a series of posts by Shep, Jon K, and DALeffler, in August 2002 at this very forum. Evidently I stumbled into the "right" combination of words with a Google search to hit this post. My earlier attempts at searching this forum had not worked.

According to DALeffler the IIF statement fails because: "The reason is because a function (or an Iif, or what have you) is returning a string or a number, not an SQL operator." So the like operator has to come first.

Thanks.

[Jon K]

Sometimes there are more than one way to achieve the same results.

If you build the query in query Design View in the same way as in my post above, the True should work. You can see my sample database in the link in my earlier post as well as the database attached in post #4 in this thread:-
http://www.access-programmers.co.uk/...d.php?t=133472

The 2002 thread that you quoted was discussion about building a query in SQL View.


In an IIF, True can return every record including Null values.

Like "*" is slightly different. It excludes Null values. So it works as long as the field doesn't contain Null values. But if the field contains Null values, it simply fails to return every record. See Note (2) in the link in my earlier post.
.