jet database engine does not recognize...

[fd1]

I have a query that works fine when it's run on its own but when it's included within another query it breaks the other query and I get the following error:

The jet database engine does not recognize 'tblComments.refSAR_ID' as a valid field name or expression

Can anyone tell me what am I doing wrong here?


This is the query in question:

SELECT tblBalance.SAR_ID, tblComments.Comment
FROM tblBalance LEFT JOIN tblComments ON tblBalance.SAR_ID = tblComments.refSAR_ID
WHERE (((tblComments.CDate)=(SELECT Max(X.CDate) FROM tblComments As X WHERE X.refSAR_ID = tblComments.refSAR_ID) Or (tblComments.CDate) Is Null));
 

This is the code for the the other query:

SELECT tblBalance.SAR_ID, qryConst.Team, qryConst.[Team Code]
FROM qryConst, tblBalance INNER JOIN qryComm ON tblBalance.SAR_ID = qryComm.SAR_ID;
 

qryConst is a crosstab query and from what I can tell, I'm getting the error whenever qryConst and qryComm (from my original post) are together.

and here's the code for the crosstab query qryConst:

TRANSFORM First(xtblConst.Value) AS FirstOfValue
SELECT xtblConst.order
FROM xtblConst
GROUP BY xtblConst.order
PIVOT xtblConst.Title;