Data type mismatch in criteria expression

[Guest]

I have a simple unmatched query that looks for policy numbers in one table
(actually it's a query on a table) that don't have a match in another table
(again a query on a table). When I use "Is Null" on the criteria of the
field that has the missing policies, I get a "Data type mismatch in criteria
expression" error. If I take the Is Null off, the query will run but will
show #Error for all the records that should be Null.
I've tried several different thing to get around this but nothing seems to
be working.
I've tried using a IIF statement to test for an error
(IIF(ISERROR([Query2].[Policy]),1,0). The results show 0 for matched records
and again #Error for unmatched records. If I put <>0 on the criteria line I
still get the "Data type mismatch in criteria expression" error again.
I've also tried using CVar() on both of the source queries to force the
policy field of each query to be a variant and the same results occur.
Does anyone have any idea what could be going on? Thank you.

Jeff

 

[[MVP] S.Clark]

Try using the Nz() function.

e.g. Nz(FieldName, "")

 

[Guest]

Nope, that doesn't work. Still get the "Data type mismatch" error.

Try using the Nz() function.

e.g. Nz(FieldName, "")

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I have a simple unmatched query that looks for policy numbers in one table
(actually it's a query on a table) that don't have a match in another
table
(again a query on a table). When I use "Is Null" on the criteria of the
field that has the missing policies, I get a "Data type mismatch in
criteria
expression" error. If I take the Is Null off, the query will run but will
show #Error for all the records that should be Null.
I've tried several different thing to get around this but nothing seems to
be working.
I've tried using a IIF statement to test for an error
(IIF(ISERROR([Query2].[Policy]),1,0). The results show 0 for matched
records
and again #Error for unmatched records. If I put <>0 on the criteria line
I
still get the "Data type mismatch in criteria expression" error again.
I've also tried using CVar() on both of the source queries to force the
policy field of each query to be a variant and the same results occur.
Does anyone have any idea what could be going on? Thank you.

Jeff

 

[[MVP] S.Clark]

Post your SQL clause, so we can see if you applied it appropriately. You
can put anything you want for the second parameter. I put "", but if you
need a date or number, then put it there.

Nope, that doesn't work. Still get the "Data type mismatch" error.

Try using the Nz() function.

e.g. Nz(FieldName, "")

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I have a simple unmatched query that looks for policy numbers in one
table
(actually it's a query on a table) that don't have a match in another
table
(again a query on a table). When I use "Is Null" on the criteria of
the
field that has the missing policies, I get a "Data type mismatch in
criteria
expression" error. If I take the Is Null off, the query will run but
will
show #Error for all the records that should be Null.
I've tried several different thing to get around this but nothing seems
to
be working.
I've tried using a IIF statement to test for an error
(IIF(ISERROR([Query2].[Policy]),1,0). The results show 0 for matched
records
and again #Error for unmatched records. If I put <>0 on the criteria
line
I
still get the "Data type mismatch in criteria expression" error again.
I've also tried using CVar() on both of the source queries to force the
policy field of each query to be a variant and the same results occur.
Does anyone have any idea what could be going on? Thank you.

Jeff

 

[Guest]

SELECT qry1998Ledger_IND_Maturities_TrimPolicy.POLICY,
Nz([qryMaturity1998].[Policy],"") AS [Check]
FROM qry1998Ledger_IND_Maturities_TrimPolicy LEFT JOIN qryMaturity1998 ON
qry1998Ledger_IND_Maturities_TrimPolicy.POLICY = qryMaturity1998.Policy
WHERE (((Nz([qryMaturity1998].[Policy],""))=""));

Thanks for looking.

Post your SQL clause, so we can see if you applied it appropriately. You
can put anything you want for the second parameter. I put "", but if you
need a date or number, then put it there.

Nope, that doesn't work. Still get the "Data type mismatch" error.

Try using the Nz() function.

e.g. Nz(FieldName, "")

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I have a simple unmatched query that looks for policy numbers in one
table
(actually it's a query on a table) that don't have a match in another
table
(again a query on a table). When I use "Is Null" on the criteria of
the
field that has the missing policies, I get a "Data type mismatch in
criteria
expression" error. If I take the Is Null off, the query will run but
will
show #Error for all the records that should be Null.
I've tried several different thing to get around this but nothing seems
to
be working.
I've tried using a IIF statement to test for an error
(IIF(ISERROR([Query2].[Policy]),1,0). The results show 0 for matched
records
and again #Error for unmatched records. If I put <>0 on the criteria
line
I
still get the "Data type mismatch in criteria expression" error again.
I've also tried using CVar() on both of the source queries to force the
policy field of each query to be a variant and the same results occur.
Does anyone have any idea what could be going on? Thank you.

Jeff