We help IT Professionals succeed at work.

Getting "type mismatch in criteria expression" on a right join query

Hi Experts,

I am trying to create a view and getting datatype mismatch

V_Screening_View
SELECT CStr("-" & Mid([Caregivers_CaregiverCode],InStr([Caregivers_CaregiverCode],"-")+1)) AS CG_Code, InStr([Caregivers_CaregiverCode],"-") AS 1, V_Screening_Covid.*
FROM V_Screening_Covid
WHERE (((InStr([Caregivers_CaregiverCode],"-"))<>0));

Open in new window

VisitReport_View
SELECT "-" & CStr(Replace(Mid([Caregiver],InStr([caregiver],"(")+1),")","")) AS CG_Code, [Visit Report].*
FROM [Visit Report]
WHERE ((([Visit Report].Caregiver) Is Not Null));

Open in new window


Now this one is giving me the error

SELECT VisitReport_View.*
FROM V_Screening_View RIGHT JOIN VisitReport_View ON V_Screening_View.CG_Code = VisitReport_View.CG_Code
WHERE (((V_Screening_View.CG_Code) Is Null));

Open in new window


Any idea?

Thanks
Comment
Watch Question

Tom FarrarConsultant
CERTIFIED EXPERT

Commented:
My guess is what you are matching on (two fields) are different data types and they need to be the same.
CERTIFIED EXPERT

Author

Commented:
FYI- Both files are linked CSV files and both fields are defined as short text.

Thanks,
Ben
CERTIFIED EXPERT
Top Expert 2014

Commented:
Are there any Nulls in that field?
CERTIFIED EXPERT

Commented:
Try different query please:
SELECT *
  FROM VisitReport_View
 WHERE CG_Code NOT IN (SELECT CG_Code FROM V_Screening_View WHERE CG_Code IS NOT NULL);

Open in new window

BTW, to have column name "1" does not seems to be a good approach because it is rather confusing. You should enclose it into brackets at least.
CERTIFIED EXPERT

Author

Commented:
@Aikimark,
Possible.
@ pcelba ,
I ended up doing that but afraid it may take long as the list grows, any alternatives?
Thanks,
Ben
CERTIFIED EXPERT

Commented:
The calculated column CG_Code is not indexed so there will be almost no speed difference between RIGHT JOIN and NOT IN.

Of course, if you would like to optimize the query then you may add this column to both tables, index it, and calculate its values in the code. Access allows to define calculated fields in tables but you cannot index them.
Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
CERTIFIED EXPERT

Commented:
Can you run the views individually without any errors?

for examples:

select count(*) from V_Screening_View

Open in new window


select count(*) from VisitReport_View

Open in new window

CERTIFIED EXPERT

Author

Commented:
Hi,

" Access allows to define calculated fields in tables but you cannot index them "
Then I guess this is not an option as those tables are linked to CSV files.

" Can you run the views individually without any errors? "
Yes.

Thanks,
Ben

CERTIFIED EXPERT

Commented:
Is there a reason you are using linked csv files. Can you import to tables? You can then define indexes and improve performance.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You expressions don't match.

CStr("-" & Mid([Caregivers_CaregiverCode],InStr([Caregivers_CaregiverCode],"-")+1)) 
"-" & CStr(Replace(Mid([Caregiver],InStr([caregiver],"(")+1),")",""
The first will/should always return a string, but the second may return a number like -3456, thus the data types will not match.
We don't know your caregiver code, so it is not possible to advice how to correct it. 
CERTIFIED EXPERT

Commented:
@Gustav, are you saying Access produces numeric output when you combine two strings and the results is convertible to digit? And this may happen inside one column data?

@bfuchs - I did not propose calculated column as a solution. You may either use NOT IN instead of RIGHT JOIN or create two new tables with indexed CG_Code column and load these tables from your CSVs which then allows the optimized RIGHT JOIN hopefully.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
are you saying Access produces numeric output when you combine two strings and the results is convertible to digit? And this may happen inside one column data? 
Yes. In SQL, a text value must be quoted:

Select -4567 As SomeNumber, '-3456' As SomeText, ...
Select CStr(-789) As SomeNumber, "'" & Cstr(-345) & "'" As SomeText ...

CERTIFIED EXPERT

Commented:
OK, thanks. Is this rule valid for MS Access tables? In other words it would mean these tables can contain text and numbers mixed in one data column. This is rather Excel sheet than SQL table. But we may see similar behavior in e.g. SQLite.

Then it should be sufficient to add the apostrophe in the front of the CG_Code column (or enclose it into apostrophes) in both views to solve the conversion issue in RIGHT JOIN.
CERTIFIED EXPERT

Author

Commented:
Hi Experts,

Is there a reason you are using linked csv files. Can you import to tables?
This is meant for users to download two CSV files from two diferrent platforms and compare, dont want make them do extra work...

"We don't know your caregiver code, so it is not possible to advice how to correct it. "

One table has it like this
EmployeeName (10014)
The other like this
WGC-10014 (employee name is in a separate column).

Tried to change for the following, same problem.
CG_Code: CStr("'" & Mid([Caregivers_CaregiverCode],InStr([Caregivers_CaregiverCode],"-")+1) & "'")
Thanks,
Ben

CERTIFIED EXPERT

Commented:
So if the format is almost fixed you may use following formulas:

For the EmployeeName (nnnnnnnn) format:

CLng(Mid([Caregiver], InStr([Caregiver],"(")+1, InStr([Caregiver],")")-InStr([Caregiver],"(")-1))

Open in new window


For the XXXX-nnnnnn format:
CLng(RTrim(Mid([Caregivers_CaregiverCode], InStr([Caregivers_CaregiverCode],"-")+1)))

Open in new window


Columns data type should be Long integer which means no conversion in RIGHT JOIN.

You should make sure the conversion works correctly as the first step.
CERTIFIED EXPERT

Author

Commented:
Changed to the following

SELECT CLng(RTrim(Mid([Caregivers_CaregiverCode],InStr([Caregivers_CaregiverCode],"-")+1))) AS CG_Code, InStr([Caregivers_CaregiverCode],"-") AS 1, V_Screening_Covid.*, V_Screening_Covid.Screening_COVID_Date_Created AS Expr1
FROM V_Screening_Covid
WHERE (((InStr([Caregivers_CaregiverCode],"-"))<>0) AND ((V_Screening_Covid.Screening_COVID_Date_Created)>=Date()));

SELECT CLng(Mid([Caregiver],InStr([Caregiver],"(")+1,InStr([Caregiver],")")-InStr([Caregiver],"(")-1)) AS CG_Code, [Visit Report].*, InStr([caregiver],"(") AS Expr1
FROM [Visit Report]
WHERE (((InStr([caregiver],"("))<>0) AND (([Visit Report].Caregiver) Is Not Null));

And same problem on below

SELECT VisitReport_View.*, V_Screening_View.CG_Code
FROM VisitReport_View LEFT JOIN V_Screening_View ON VisitReport_View.CG_Code = V_Screening_View.CG_Code
WHERE (((V_Screening_View.CG_Code) Is Null));


Thanks,
Ben
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Join on these two expressions that should work also for "invalid" or empty strings:

n = "EmployeeName (10014)"
? Val(Mid(n, InStr(n, "(") + 1))
 10014


m = "WGC-10014"
? Val(Mid(m, InStr(m, "-") + 1))
 10014


CERTIFIED EXPERT

Author

Commented:
Changed for following

SELECT Val(Mid([Caregivers_CaregiverCode],InStr([Caregivers_CaregiverCode],"-")+1)) AS CG_Code, InStr([Caregivers_CaregiverCode],"-") AS 1, V_Screening_Covid.*, V_Screening_Covid.Screening_COVID_Date_Created AS Expr1
FROM V_Screening_Covid
WHERE (((InStr([Caregivers_CaregiverCode],"-"))<>0) AND ((V_Screening_Covid.Screening_COVID_Date_Created)>=Date()));



SELECT Val(Mid([caregiver],InStr([caregiver],"(")+1)) AS CG_Code, [Visit Report].*, InStr([caregiver],"(") AS Expr1
FROM [Visit Report]
WHERE (((InStr([caregiver],"("))<>0) AND (([Visit Report].Caregiver) Is Not Null));


Same issue
Untitled.png



Thanks,
Ben


CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Then you probably have Null values. Nz can handle those:

n = "EmployeeName (10014)"
? Val(Mid(Nz(n), InStr(Nz(n), "(") + 1))
 10014


m = "WGC-10014"
? Val(Mid(Nz(m), InStr(Nz(m), "-") + 1))
 10014


CERTIFIED EXPERT

Author

Commented:
Okay now the below is not giving any error.

SELECT VisitReport_View.*, V_Screening_View.CG_Code
FROM VisitReport_View LEFT JOIN V_Screening_View ON VisitReport_View.CG_Code = V_Screening_View.CG_Code
WHERE (((V_Screening_View.CG_Code) Is Null));

However its not returning any records either, and its not possible as the table Visit_Report has 1800 (almost no dups) records and Screening only has 37

Thanks,
Ben
CERTIFIED EXPERT

Author

Commented:
The below seems to be working.

SELECT VisitReport_View.*, V_Screening_View.CG_Code
FROM VisitReport_View LEFT JOIN V_Screening_View ON VisitReport_View.CG_Code = V_Screening_View.CG_Code
WHERE (((V_Screening_View.CG_Code) Is Null Or (V_Screening_View.CG_Code)=0));

Any explanation...?
Thanks,
Ben
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Hard to tell without having the data ...
CERTIFIED EXPERT

Author

Commented:
Thank you!
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!