Wildcarding in an IIf Statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TESA0_4 - 06 May 2008 03:40 GMT

I have a table where work done on aircraft is broken down into zones and
qualifiers applicable to those zones. For example 'fuselage' has qualifiers
such as 'front', rear', 'flight deck'etc.
Users need to query the data based on zone and qualifiers.
I have created a form where users can select zones and then get a filtered
list of qualifiers applicable to the zone. They have the option of selecting
up to three qualifiers for a zone. But I also want them to have the option
for selecting all records for a zone irrespective of qualifier. With this in
mind my form has a flag for selecting 'All Qualifiers'.

In the Design View for the query I have entered the following criterion for
the Qualifier field:
IIf([Forms]![frmAnalysis]![FlagAll]=-1, Like
"*",[Forms]![frmAnalysis]![QualifierA] Or [Forms]![frmAnalysis]![QualifierB]
Or [Forms]![frmAnalysis]![QualifierC])

If I enter a value in the form for QualifierA, B and/or C, the results are
fine. If no Qualifier values are entered and the Flag is set to Yes, the
query returns no records. Why is 'Like "*"' not returning a wildcard search
for any Qulaifier value?

Any sugesstions gratefully received.

Reply to this Message

John W. Vinson - 06 May 2008 07:51 GMT

>IIf([Forms]![frmAnalysis]![FlagAll]=-1, Like
>"*",[Forms]![frmAnalysis]![QualifierA] Or [Forms]![frmAnalysis]![QualifierB]
[quoted text clipped - 4 lines]
>query returns no records. Why is 'Like "*"' not returning a wildcard search
>for any Qulaifier value?

Because IIF can only return an actual value to be searched - NOT an operator
such as LIKE or OR.

Try turning the logic around: use a criterion of

IN ([Forms]![frmAnalysis]![QualifierA], [Forms]![frmAnalysis]![QualifierB],
[Forms]![frmAnalysis]![QualifierC]) OR [Forms]![frmAnalysis]![FlagAll]=-1

This will return all records if FlagAll is True (since the criterion evaluates
to TRUE regardless of the table contents); if FlagAll isn't TRUE it uses the
IN syntax to match any of the three form controls.

Signature

John W. Vinson [MVP]

Reply to this Message

TESA0_4 - 06 May 2008 08:47 GMT

Thanks for your response John.

I have inserted your suggestion and it does exactly what I want. However, I
don't understand why it works!

My presumption was that an expression used to determine a Query condition
needed to resolve to a value that in someway related to the values
potentially stored in the field. Your expression shows me that an expression
that resolves to 'True' allows all values stored in the field. I don't really
understand why this should be so but I can understand the usefulness of
arrangement.

Thanks for solving my immediate problem and expanding my understanding of
Access.

Regards,

Terry

> I have a table where work done on aircraft is broken down into zones and
> qualifiers applicable to those zones. For example 'fuselage' has qualifiers
[quoted text clipped - 18 lines]
>
> Any sugesstions gratefully received.

Reply to this Message

John W. Vinson - 07 May 2008 06:15 GMT

>My presumption was that an expression used to determine a Query condition
>needed to resolve to a value that in someway related to the values
>potentially stored in the field. Your expression shows me that an expression
>that resolves to 'True' allows all values stored in the field. I don't really
>understand why this should be so but I can understand the usefulness of
>arrangement.

That is a little appreciated feature of SQL queries. A SQL query may have a
WHERE clause (and/or a HAVING clause if it's a totals query).

The WHERE clause is a logical expression which must evaluate to either TRUE or
FALSE.

If it's TRUE, the current record is included in the query recordset. If it's
FALSE, it isn't.

There is *nothing* in the specifications that requires that the WHERE clause
must reference table fields!

There are in fact circumstances where it makes sense to use

SELECT <whatever> FROM table WHERE TRUE;

to retrieve all records, or

SELECT <whatever> FROM table WHERE FALSE;

to intentionally retrieve an empty recordset (say because you're going to use
it to add records via a form or code).

Signature

John W. Vinson [MVP]

Reply to this Message

TESA0_4 - 07 May 2008 06:41 GMT

Hi John,
Thank you for the explanation. I understand the concept but at this stage my
skills with writing SQL are very limited. I'm still at the stage of mastering
the use the criteria grid in the Query Design View and from time to time
referring to the SQL view to try and comprehend the code that is generated.
For example, since receiving your reply yesterday I have mastered the concept
incorporating 'flag fields' in the grid (the way in which Access 'interprets'
the code you suggested yesterday).

Regards,
Terry

> >My presumption was that an expression used to determine a Query condition
> >needed to resolve to a value that in someway related to the values
[quoted text clipped - 25 lines]
> to intentionally retrieve an empty recordset (say because you're going to use
> it to add records via a form or code).

Reply to this Message