Syntax of subquery problem

[girky]

Hello,
I'm having a problem with the syntax of a subquery. This is what I have:

SELECT tblErrorDetail.ID
FROM tblErrorDetail
WHERE tblErrorDetail.[Reason Code] In (select [tblReasonCodes].[Reason Code] from tblReasonCodes where [BA]= "P");

I'm getting a type mismatch error. Reasoncode is a numeric field.

When I substitute with an example it works fine and my subquery works fine by iteself:

SELECT tblErrorDetail.ID
FROM tblErrorDetail
WHERE tblErrorDetail.[Reason Code] In ('741');


Can someone help me?
Thanks!

 

[dhookom]

Your SQL suggests Reason Code is text since it is enclosed in single quotes. I would check both tables to find out which is text and which is numeric and then create a plan to change one.

SELECT tblErrorDetail.ID
FROM tblErrorDetail
WHERE tblErrorDetail.[Reason Code] In ('741');

Duane
Hook'D on Access
MS Access MVP