WHERE query retrurning wrong results

[L0stAngel]

Hi, I thought this would be easy as I have done where clauses before but for some reason it's not working.

I have 3 tables. A Query gathers data from each table. I have a 4th table (tblTest) that has two columns. TID and TCode. I want to have it so when I run my query, that it will not return results where (ie: mcode.mCode = tblTest.TCode AND mid.mid = tblTest.Tid)

Right now, I have this as my query:

SELECT
MIDs.MID, MCodes.MCode, MCodes.MaintDesc, MCodes.ICycle, MRecord.TimeLogged, MRecord.CumCycle, MIDs.LastUpdt, MRecord.SelInterval, MRecord.ICycle, MRecord.MNo, MCodes.SelInterval

FROM 
MIDs, MRecord, MCodes

WHERE
(((MIDs.MNo)=[MRecord].[MNo]) 

AND
((MRecord.MCNo)=[MCodes].[MCNo] 

AND
(MRecord.MCNo)>0))

ORDER BY
MIDs.MID, MCodes.MCode;

 

[PHV]

SELECT MIDs.MID, MCodes.MCode, MCodes.MaintDesc, MCodes.ICycle, MRecord.TimeLogged, MRecord.CumCycle, MIDs.LastUpdt, MRecord.SelInterval, MRecord.ICycle, MRecord.MNo, MCodes.SelInterval
FROM ((MIDs
INNER JOIN MRecord ON MIDs.MNo = MRecord.MNo)
INNER JOIN MCodes ON MRecord.MCNo = MCodes.MCNo)
LEFT JOIN tblTest ON MCodes.MCode = tblTest.TCode AND MIDs.MID = tblTest.Tid
WHERE MRecord.MCNo > 0 AND tblTest.TCode Is Null
ORDER BY MIDs.MID, MCodes.MCode;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[L0stAngel]

It says Join expression not Supported.