Data type mismatch in criteria expression

I'm trying to use an expression as the criteria in a query field and I can't
understand why I'm getting a data type mismatch.

The field is a text field.

The expression I'm trying to use is:

IIf(DLookUp("Processed","tblFromToInvoiceAandP","F romToID = 1")=-1,Null,Is
not Null).

I know there's nothing wrong with the DLookUp function, because I've checked
it in the VBA Immediate Window. In addition, both Null and Is not Null work
just fine if I type either in by themselves. I know there's nothing wrong
with the IIf structure, because if I replace Null and Is not Null with
specific text values, there is no error and the query recordset returns the
records that have the criteria valuse.

So why am I getting a "data type mismatch" error when I put the Null/Is not
Null values in the IIf statement?

Thanks in advance,

Paul

I tried several different forms for this criteria expression, and the
following expression works in the Immediate Window:

?IIf(DLookUp("Processed","tblFromToInvoiceAandP"," FromToID =
1")=-1,Null,"Not Null")

That is, if the DLookUp table field has a value of -1, VBA displays the
following:

Null

If the table field has a value other than one, (0), VBA displays

Not Null

But it still doesn't work in the query. When I go to datasheet view, there
is no error message, but the query doesn't display any records, regardless
of whether the DLookUp table field has a value of 0 or -1.

Can anyone tell me how I can modify this expression so it will return the
desired records?

Thanks again in advance.

Paul

You can see that in your DLookUp, the DLookUp returns Null (of undefined
type) for the True part and "Is Not Null" of String Type. Since there are 2
different types possible in the return, DLookUp() returns Variant. Most of
the time, JET/Access type-casts the Variant return of DLookUp to the correct
type for you. Unfortunately, not in this case.

Since your FieldX is a Text Field and you can't see the difference between
Null and an Empty String, my suggestion is to use the length of FieldX
instead.

Create a Calculated Field (preferably, not displayed) in your Query:

FldXNotEmpty: (Len(Trim$([FieldX] & "")) 0)

In the criteria of the above Calculated Field, use:

IIf(DLookUp("Processed","tblFromToInvoiceAandP","F romToID = 1")=-1, False,
True)

This way, when Processed = -1 (True), you get all Records whose FieldX value
is either Null, Empty String or white spaces.

--
HTH
Van T. Dinh
MVP (Access)




"Paul James" wrote in message
...
I tried several different forms for this criteria expression, and the
following expression works in the Immediate Window:

?IIf(DLookUp("Processed","tblFromToInvoiceAandP"," FromToID =
1")=-1,Null,"Not Null")

That is, if the DLookUp table field has a value of -1, VBA displays the
following:

Null

If the table field has a value other than one, (0), VBA displays

Not Null

But it still doesn't work in the query. When I go to datasheet view,
there
is no error message, but the query doesn't display any records, regardless
of whether the DLookUp table field has a value of 0 or -1.

Can anyone tell me how I can modify this expression so it will return the
desired records?

Thanks again in advance.

Paul

You're right, Van, it works just fine.

Thanks for the solution, and thanks also for the clear explanation.

Paul

Dear Paul:

Your IIf statement suggests that the phrase "Is not Null" is a value.
It isn't. If the DLookup succeeds, what value do you want to assign?
Perhaps you want the value of the lookup assigned. If so:

IIf(DLookUp("Processed","tblFromToInvoiceAandP",
"FromToID = 1")=-1, Null,
DLookUp("Processed","tblFromToInvoiceAandP","FromT oID = 1"))

Does this help?

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


On Fri, 3 Sep 2004 14:29:29 -0700, "Paul James"
wrote:

I'm trying to use an expression as the criteria in a query field and I can't
understand why I'm getting a data type mismatch.

The field is a text field.

The expression I'm trying to use is:

IIf(DLookUp("Processed","tblFromToInvoiceAandP"," FromToID = 1")=-1,Null,Is
not Null).

I know there's nothing wrong with the DLookUp function, because I've checked
it in the VBA Immediate Window. In addition, both Null and Is not Null work
just fine if I type either in by themselves. I know there's nothing wrong
with the IIf structure, because if I replace Null and Is not Null with
specific text values, there is no error and the query recordset returns the
records that have the criteria valuse.

So why am I getting a "data type mismatch" error when I put the Null/Is not
Null values in the IIf statement?

Thanks in advance,

Paul

Thanks for your reply and suggestion, Tom. Although Van's solution worked
quite well, I finally decided to make some design changes to my form and
query which made it possible to obtain the results with a simpler criteria
expression that didn't require an IIf clause. All I now have to do is set
the criteria expression to the value of a checkbox in the form, while the
criteria field is itself a Yes/No field.

However, I tried testing the expression you suggested:

IIf(DLookUp("Processed","tblFromToInvoiceAandP",
"FromToID = 1")=-1, Null,
DLookUp("Processed","tblFromToInvoiceAandP","FromT oID = 1"))

and it correctly displays records when the IIf condition is false, that is,
when the value is 0, in which case the expression returns a value of zero,
which produces the desired records. However, when the expression is true,
it doesn't return any records. I believe the reason for this is that the
field being tested is a Yes/No (checkbox) field, and it can only have values
of -1 or zero. So if the criteria has a value of Null, it doesn't return
any records.

Again, thanks for responding to my original question.

Paul