Query Result not as desired 1

[Mahathma]

In a query, I use a field from another table as criteria.
If I use the table column address, it works fie.
But, when I prefix it with 'NOT' or 'NOT LIKE', it does not filter the records. The query displays all records.
Is NOT or NOT LIKE the correct syntax?
Thanks
Guru

 

[cmmrfrds]

Paste in your query so it can be evaluated.

 

[Mahathma]

tbl_TC contains the following fields
TC_Id
TC_Currency
TC_Amount
Conv_Currency
Conv_Amount

tbl_TCAdvance contains the following fields
TC_ID
Adv_Currency
Adv_Amount

I build a query taking all fields from tbl_TC
with a join on TC_ID from tbl_TC and TC_ID from tbl_TCAdvance,
and
[tblTCAdvance]![Adv_Currency] as criteria for TC_Currency.
Result is as desired.

If I add 'Not' or 'Not Like' before criteria, it does not exclude the records with the entries. The query displays all records.
Please help and thanks in advance.
If this is not enough, please tell me a way of pasting snapshots on this posting and I shall do so.
Guru

 

[cmmrfrds]

What does the select statement look like. Go into query designer and select sql view. Paste the sql.

Something like this??
Select tbl_TC.* from tbl_TC
INNER JOIN tbl_TCAdvance
ON tbl_TC.TC_ID = tbl_TCAdvance.TC_ID
Where
NOT (tbl_TC.TC_Currency = tblTCAdvance.Adv_Currency)

 

[Mahathma]

Please find below the SQL statement.
Thanks for your interest and help.

SELECT tblTravelCheque.TravelID, tblTravelCheque.TCCurrency, Sum(tblTravelCheque.TCAmount) AS SumOfTCAmount, tblTravelCheque.ConvCurrency, Sum(tblTravelCheque.ConvAmount) AS SumOfConvAmount, 1/([SumOfTCAmount]/[SumOfConvAmount]) AS ExchRateToTCAmount
FROM tblTravelCheque INNER JOIN tblTravelAdvance ON tblTravelCheque.TravelID = tblTravelAdvance.TravelID
WHERE (((tblTravelCheque.TCCurrency)=[tblTravelAdvance]![AdvanceCurrency]))
GROUP BY tblTravelCheque.TravelID, tblTravelCheque.TCCurrency, tblTravelCheque.ConvCurrency;
Guru

 

[cmmrfrds]

Why do you have ! in the field definition?

WHERE (((tblTravelCheque.TCCurrency)=[tblTravelAdvance]![AdvanceCurrency]))

WHERE (((tblTravelCheque.TCCurrency)=tblTravelAdvance.AdvanceCurrency))