2 condition

M

mavis

Hi All,

I have the below code. what i need is to pull out all record exclude those
which ClaimStatus does not equal to "Closed" or "Paid" and ClaimedAmount is
not null. When i run the below code, record with "Paid" and 'Closed" Status
still show.

Please kindly advice. Thanks in advance.

SELECT TBLCargoClaims.Vessel, TBLCargoClaims.Voyage, TBLCargoClaims.ID,
TBLValueClaim.ClaimedAmount, TBLValueClaim.ClaimCurrency,
TBLValueClaim.ValuableROE,
[TBLP&IVoyAccurmulativeRecovery].DeductibleApplied, TBLCargoClaims.ClaimStatus
FROM (((TBLCargoClaims LEFT JOIN TBLValueClaim ON
(TBLCargoClaims.BillofLading=TBLValueClaim.BillOfLading) AND
(TBLCargoClaims.ID=TBLValueClaim.ClaimNumber)) LEFT JOIN
[TBLP&IVoyAccurmulativeRecovery] ON
(TBLCargoClaims.Voyage=[TBLP&IVoyAccurmulativeRecovery].Voyage) AND
(TBLCargoClaims.Vessel=[TBLP&IVoyAccurmulativeRecovery].Vessel)) LEFT JOIN
[TBLP&IRecov] ON TBLCargoClaims.ID=[TBLP&IRecov].ClaimID) INNER JOIN
TBLRecovery3rdParty ON TBLCargoClaims.ID=TBLRecovery3rdParty.ClaimNumber
WHERE (((TBLValueClaim.ClaimedAmount) Is Not Null) AND
((TBLCargoClaims.ClaimStatus)<>"Closed")) OR (((TBLValueClaim.ClaimedAmount)
Is Not Null) AND ((TBLCargoClaims.ClaimStatus)<>"Paid"));
 
A

Allen Browne

Try changing the WHERE clause like this:
WHERE (TBLValueClaim.ClaimedAmount Is Not Null)
AND (TBLCargoClaims.ClaimStatus NOT IN ("Closed", "Paid"));

The way it was, records where ClaimStatus is "Closed" still meet the
criterion that ClaimStatus not "Paid" and so they get included when you use
OR (i.e. meet either criterion.)
 
M

mavis

HiAllen,

Thanks! It works great.


Allen Browne said:
Try changing the WHERE clause like this:
WHERE (TBLValueClaim.ClaimedAmount Is Not Null)
AND (TBLCargoClaims.ClaimStatus NOT IN ("Closed", "Paid"));

The way it was, records where ClaimStatus is "Closed" still meet the
criterion that ClaimStatus not "Paid" and so they get included when you use
OR (i.e. meet either criterion.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
mavis said:
Hi All,

I have the below code. what i need is to pull out all record exclude those
which ClaimStatus does not equal to "Closed" or "Paid" and ClaimedAmount
is
not null. When i run the below code, record with "Paid" and 'Closed"
Status
still show.

Please kindly advice. Thanks in advance.

SELECT TBLCargoClaims.Vessel, TBLCargoClaims.Voyage, TBLCargoClaims.ID,
TBLValueClaim.ClaimedAmount, TBLValueClaim.ClaimCurrency,
TBLValueClaim.ValuableROE,
[TBLP&IVoyAccurmulativeRecovery].DeductibleApplied,
TBLCargoClaims.ClaimStatus
FROM (((TBLCargoClaims LEFT JOIN TBLValueClaim ON
(TBLCargoClaims.BillofLading=TBLValueClaim.BillOfLading) AND
(TBLCargoClaims.ID=TBLValueClaim.ClaimNumber)) LEFT JOIN
[TBLP&IVoyAccurmulativeRecovery] ON
(TBLCargoClaims.Voyage=[TBLP&IVoyAccurmulativeRecovery].Voyage) AND
(TBLCargoClaims.Vessel=[TBLP&IVoyAccurmulativeRecovery].Vessel)) LEFT JOIN
[TBLP&IRecov] ON TBLCargoClaims.ID=[TBLP&IRecov].ClaimID) INNER JOIN
TBLRecovery3rdParty ON TBLCargoClaims.ID=TBLRecovery3rdParty.ClaimNumber
WHERE (((TBLValueClaim.ClaimedAmount) Is Not Null) AND
((TBLCargoClaims.ClaimStatus)<>"Closed")) OR
(((TBLValueClaim.ClaimedAmount)
Is Not Null) AND ((TBLCargoClaims.ClaimStatus)<>"Paid"));
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update Query 10
Query from 2 tables 4
Parameter from Form 6
Query not Updateable 1

Top