If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Data type mismatch in criteria expression
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 |
#2
|
|||
|
|||
Try using the Nz() function.
e.g. Nz(FieldName, "") -- Steve Clark, Access MVP FMS, Inc. www.fmsinc.com/consulting "Jeff" wrote in message ... 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 |
#3
|
|||
|
|||
Nope, that doesn't work. Still get the "Data type mismatch" error.
"[MVP] S.Clark" wrote: Try using the Nz() function. e.g. Nz(FieldName, "") -- Steve Clark, Access MVP FMS, Inc. www.fmsinc.com/consulting "Jeff" wrote in message ... 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 |
#4
|
|||
|
|||
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" wrote in message ... Nope, that doesn't work. Still get the "Data type mismatch" error. "[MVP] S.Clark" wrote: Try using the Nz() function. e.g. Nz(FieldName, "") -- Steve Clark, Access MVP FMS, Inc. www.fmsinc.com/consulting "Jeff" wrote in message ... 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 |
#5
|
|||
|
|||
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" wrote: 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" wrote in message ... Nope, that doesn't work. Still get the "Data type mismatch" error. "[MVP] S.Clark" wrote: Try using the Nz() function. e.g. Nz(FieldName, "") -- Steve Clark, Access MVP FMS, Inc. www.fmsinc.com/consulting "Jeff" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
strategy for data entry in multiple tables | LAF | Using Forms | 18 | April 25th, 2005 04:04 AM |
"Data Type Mismatch In Criteria Expression" Error | LB | Running & Setting Up Queries | 1 | November 9th, 2004 03:02 PM |
Data type mismatch in criteria expression | Paul James | Running & Setting Up Queries | 5 | September 8th, 2004 01:13 AM |
data type mismatch error | Praveen Manne | Using Forms | 3 | June 30th, 2004 11:02 PM |