Data type mismatch in criteria expression for the subquery with calculated expression

Question

I am trying to create a SQL subquery in MS Access. Getting "type mismatch in expression
criteria" error message.

Subquery is

SELECT *
  (SELECT Locs_Eq.Locs_Eq 
   FROM Locs_Eq 
   WHERE Query6.StDeExtrREF=CStr(Locs_Eq.Full_VendSN)
  ) AS Loc_1
FROM Query6

The variable DeExtrREF from Query6 is as follows:

 IIF(PDetail.VeName="Siem" AND [DeExtr1]="Contr REF#",
     TRIM(MID (PDetail.Desc, (InStr([PDetail.Desc],":")+1), LEN(PDetail.Desc))),
     TRIM(MID (PDetail.Desc, (InStr([PDetail.Desc],":")+1), LEN(PDetail.Desc))) 
    ) AS DeExtrREF

I tried adding CStr to the variables, still receive the error message. Please help.

Can you provide addition information? By itself this is a very strange looking query. Being that it is from Access I am guessing there are multiple other queries involved. If you provide some table structure and give us an idea of what you are trying to do you will probably get more help. — Lance

John Bingham John Bingham · Accepted Answer · 2013-03-01T02:20:43

I will assume that you have a comma after SELECT * on the first line. If not, change this first and see if this helps.

I am not certain, but it is probably a null-related error; Add this to a module in your access app:

public function is_null(val as variant, rplc as string) as string
    if isnull(val) then
        is_null = rplc
    else
        is_null = cstr(val)
    end if
end function

then change your query to use this, as follows:

SELECT q.*,
  (SELECT l.Locs_Eq 
   FROM Locs_Eq l 
   WHERE is_null(q.StDeExtrREF, "") = CStr(is_null(l.Full_VendSN, ""))
  ) AS Loc_1
FROM Query6 q

Hope this helps. I alias tables and queries automatically, but as a general rule you're more likely to get help here if your queries are not too difficult to understand.

Data type mismatch in criteria expression for the subquery with calculated expression

1 Answers