Although it's not the reason for its failure to work, your IIf expression
had an syntactical error in it:
IIf([forms]![frmReceipts]![chkMatched]=-1,Len(nz([Preparer]))="0",Len(nz([Pr
eparer]))>"0")
The above expression checks to see if the Len value is equal to the text
character 0, not the number 0. (You're using "0", but you should use 0.)
Although the expression service likely properly will convert "0" to the
number 0 before comparing it to the Len function's result, which is a Long
integer, not a text character.
You didn't say in your original post for which field you were using this
expression as the criterion -- I'm guessing that you were using it in the
Preparer field's "Criteria:". When you use either expression from the IIf
function's arguments on their own, the expression yields either a value of
True or False (-1 or 0) as the value that that field's values must equal. I
don't believe that that is what you wanted to have happen -- I think you
were trying to check the Len of the field not the resulting True or False.
So, your expression could be made to work, but would have to be used a
little differently from what you were trying to do.
You can see the difference between using one of the expressions on its own
in how ACCESS builds the query by creating the query and then changing to
the SQL view:
SELECT [my table].*
FROM [my table]
WHERE Len(nz([Preparer]))="0";
Notice that the WHERE clause is not trying to test the value of Preparer
field against the result of the Len function.
However, when you use the IIf expression as I believe you were trying to do,
the query that ACCESS builds is this:
SELECT [my table].*
FROM [my table]
WHERE [my
table].[Preparer]=IIf([forms]![frmReceipts]![chkMatched]=-1,Len(nz([Preparer
]))="0",Len(nz([Preparer]))>"0");
This second version actually checks the value of the Preparer field against
the resulting value from either of the IIf function's arguments. In this
case, it's looking to match exactly the value of either -1 or 0 against the
value of Preparer -- it's no longer looking to see if the Preparer field's
value has a length of 0 or greater than 0.
To use your IIf function expression, you could use a query such as this (I
wouldn't recommend this, but it's one possibility):
SELECT [my table].*, IIf([forms]![frmReceipts]![chkMatched]=-1
FROM [my table]
WHERE
IIf([forms]![frmReceipts]![chkMatched]=-1,Len(Nz([Preparer]))="0",Len(Nz([Pr
eparer]))>"0") = Not [forms]![frmReceipts]![chkMatched];
Contrast this to the one that I suggested:
SELECT [my table].*
FROM [my table]
WHERE (Len([Preparer] & "") = 0) = [Forms]![frmReceipts]![chkMatched];
My expression is doing the same thing that your IIf expression was trying to
do. There likely isn't a measurable effect on performance, but using my
expression avoids the Jet engine calling two functions (Len and Nz); instead
it just calls one (Len).
Hope that this is helpful.
--
Ken Snell
<MS ACCESS MVP>
Paul James said:
Ken - As I read your last reply, it occurred to me that the two alternative
values in an IIf statement also evaluate to either 0 or -1, which are the
same values that are produced by your equality. It just didn't occur to me
that you could obtain the result by a direct equality. Seems like it's a
more subtle way of obtaining the result.
I also noticed that you used the expression (Len([Preparer] & "") = 0) to
accomplish what I was trying to do with Len(Nz()). Does the & "" insure
that it's not null by converting null values into zero length strings? And
if so, do you have any idea of why that seems to work better than Len(Nz())
in this situation?
Paul
Ken Snell said:
The expression that I posted relies on the fact that a "checked" checkbox
has a value of True, which ACCESS stores as -1. And an "unchecked" checkbox
has a value of False, which ACCESS stores as 0.
Thus, whenever you use True, you're actually using the value of -1. And
False is 0.
So, this expression yields either True or False, which means that it
yields -1 or 0. And that matches the values from the checkbox control.
--
Ken Snell
<MS ACCESS MVP>
Paul James said:
Ken - your solution works great! It delivers the exact query results
I
was
looking for. But I can't understand HOW it works, and I just spent
the
last
two hours trying to figure that out.
Your solution is
(Len([Preparer] & "") = 0) = [Forms]![frmReceipts]![chkMatched]
I don't understand how an equation, as such, produces a criteria value that
the query can use. Obviously it does, because it works, but it's
doing
it
in a subtle way that I've not yet grasped.
I was trying to do it with a conditional IIf statement, which is intuitive
to me. That is, if the form control meets one condition, the IIf evaluates
to one value, otherwise it evaluates to another value. That, I can
understand.
But your solution is an equation, and an equation only evaluates to
true
or
false. And the [Preparer] field, to which this criteria is being applied,
is a text field, so I would have thought I'd have to set the criteria in
terms of a range of possible values that a text field can understand - like
a specific string, a series of strings, or some string formula like
Left
or
Len. But not a straight value of True or False.
So how is it that your solution delivers the correct results?
By the way, thanks (again).
Paul
