Data type mismatch in criteria expression

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Nope, that doesn't work. Still get the "Data type mismatch" error.

[MVP] S.Clark said:
Try using the Nz() function.

e.g. Nz(FieldName, "")

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

Jeff said:
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
Click to expand...
Click to expand...
 
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.

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

[MVP] S.Clark said:
Try using the Nz() function.

e.g. Nz(FieldName, "")

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

Jeff said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.

[MVP] S.Clark said:
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.

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

[MVP] S.Clark said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top