Data type mismatch in criteria expression

[Paul James]

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

 

[Paul James]

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

 

[Van T. Dinh]

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","FromToID = 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.

 

[Paul James]

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

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

Paul

 

[Tom Ellison]

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","FromToID = 1"))

Does this help?

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

 

[Paul James]

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","FromToID = 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