setting query criteria in an IIf statement

[Paul James]

I'm trying to obtain records where the criteria for a Receipt_ID field meets
the following conditions:

If a control in a form (forms!frmReceipts!chkMatched) is checked, I'd like
the criteria to be equal to the value in another control in that form
(forms!frmReceipts!txtReceiptID). Otherwise I'd like the criteria to allow
all values in the Receipt_ID field.

I've tried to use the expression

IIf([forms]![frmReceipts]![chkMatched]=-1,[forms]![frmReceipts]![ReceiptID],
Like "*")

and while this expression returns the desired records when the IIf condition
is true, it doesn't return any records when the condition is false.

The records are there in the table, so they exist. What can I put in place
of the expression Like "*" so that the query will return all records when
the IIf condition is false?

Thanks in advance,

Paul

 

[Tom Ellison]

Dear Paul:

This is easier to explain in the SQL View, since it is text there,
which I can post to the newsgroup. Your "snip" is probably from the
design grid under the Receipt_ID column of the query.

WHERE ([forms]![frmReceipts]![chkMatched] = 0
OR [Receipt_ID] = [forms]![frmReceipts]![ReceiptID])

I have put this phrase in parens in case there might be any other
criteria in the query.

It says that either the box is unchecked, or the ReceiptID is to be
filtered. That's a different logic from how you're trying to express
it, but it's the simplest logic that works.

You cannot use an operator, in this case "Like", inside the IIf
function. The operator is always "=" unless you begin the design grid
criterion with some comparison operator. Perhaps it would have worked
as:

LIKE IIf([forms]![frmReceipts]![chkMatched] = -1,
[forms]![frmReceipts]![ReceiptID], "*")

But if there is any chance of there being a special character such as

[]*?

in the control ReceiptID then this would cause problems. That's a
reason I don't do it this way. The way I prefer is what I showed you
first.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Paul James]

I don't have any special characters in the data in this field, so your
suggesting of enclosing the IIf() in the Like function worked just fine.
Specifically, I used the following as my criteria:

Like
IIf([forms]![frmReceipts]![chkMatched]=-1,[forms]![frmReceipts]![ReceiptID],
"*") Or Is Null

and it produced the desired recordset.

Thanks for showing me how to do this, Tom.
..

 

[Tom Ellison]

Dear Paul:

Please be advised to give some heed to my words of warning. Just
because you do not currently have any special characters in the data
doesn't mean you won't have them tomorrow. Unless you have done
something to prevent users from typing the special characters, it is
not unlikely you will have reported errors one day, and this will be
the cause. If you wish to avoid that, you can either restirct data
entry or write the query to not have the potential problem.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Paul James]

Thanks for the cautionary words, Tom. I have saved your explanations in my
files, because I'm sure I'll have use for this information in the future.
The reason I'm confident I won't encounter special characters in this
particular case, is that I create the data for the field in question,
(ReceiptID), as an integer using a formula based on a DMax() function. So
in this database, those values will never contain special characters.

But when I use this expression for a field that doesn't restrict the data
entry to integers, I will use the other expression you provided.

So thanks for the information.

Paul

 

[Tom Ellison]

Dear Paul:

If there is a possibility of any special characters appearing, then it
is fairly easy and painless to take care of it now, rather than
waiting for a malfunction to occur. Setting up test data and coding
for it is easy. Depending on the critical nature of the database
you're producing, you might want to consider the small amount of extra
effort now.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts