CODEWITHSUNDEEP
ms-accesstype-mismatch
RonA
RonA

Reputation: 11

MS Access - Data Type Mismatch in Criteria Expression

Using the query grid , comparing a String field with a Replace function result of another String field (same table) results in a Data Type Mismatch error when trying to filter for ‘Not Like’ (or <>). ‘TypeName’ confirms that all records are of type “String”. The problem is caused by “MyStrCalc: Replace([StrA],".","_")” which is compared with StrB. StrA contains Null for some records. These are filtered out (Criterium = “Is Not Null”). But even when creating a new query that uses the result of the first, the same error occurs. I have also tried Nz. If I use Make Table to create a new table where StrA “Is Not Null” and run effectively the same query, there’s no issue. The data in the table changes frequently, so having to create a separate table every time (tens of thousands of records) is a real nuisance. Any suggestions how to make the query work would be greatly appreciated. (By the way – the version used is MS Access 2019 under Windows 10, both with latest updates.)

Upvotes: 0

Views: 1784

Answers (1)

RonA
RonA

Reputation: 11

Thank you for your much appreciated quick reply. I tried a few things as detailed below with the fourth attempt providing the desired result.

Source table t1: | UID | StrA | StrB | | ---:| ----- | ----- | | 1 | Str.1 | Str_1 | | 2 | | Str_2 | | 3 | Str.3 | Str_4 |

Desired Result = StrA<>StrB after replacing dots in StrA with underscores: | UID | StrA | StrB | ---:| ----- | ----- | 2 | | Str_2 | 3 | Str.3 | Str_4

q1_Bad:

SELECT t1.UID, t1.StrA, t1.StrB, Replace([StrA],".","_",1,-1,1) AS StrACalc
FROM t1
WHERE (((Replace([StrA],".","_",1,-1,1)) Not Like [StrB]));

Result: “Data type mismatch in criteria expression”.

q2_Runs_CannotFilter:

SELECT t1.UID, t1.StrA, t1.StrB, Replace([StrA],".","_",1,-1,1) AS StrACalc, [StrACalc] Not Like [StrB] AS StrACalc_NtEq_StrB
FROM t1
WHERE (((t1.StrA) Is Not Null));

Result: Runs, but filtering field ‘StrACalc_NtEq_StrB’ (SQL or after running query) results in “Data type mismatch in criteria expression”.

q3_OK_SQL_FilterFail:

SELECT t1.UID, t1.StrA, t1.StrB, Replace(Nz([StrA]),".","_",1,-1,1) AS StrACalc, Nz([StrACalc] Not Like [StrB]) AS StrACalc_NtEq_StrB
FROM t1;

Result: Runs, but filtering field ‘StrACalc_NtEq_StrB’ is only possible after running query. Adding “Nz([StrACalc] Not Like [StrB]) AS StrACalc_NtEq_StrB” results in “Enter Parameter Value | StrACalc”. Note: If the result of the above is called in another query, the SQL filtering will work.

q4_OK

SELECT t1.UID, t1.StrA, t1.StrB
FROM t1
WHERE (t1.StrB) Not Like Replace(Nz([StrA]),".","_",1,-1,1);

Finally – Desired result: | UID | StrA | StrB | | ---:| ----- | ----- | | 2 | | Str_2 | | 3 | Str.3 | Str_4 |

Upvotes: 1

Related Questions