2 condition

  • Thread starter Thread starter mavis
  • Start date Start date
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"));
 
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.)
 
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"));
Click to expand...
Click to expand...
 
Back
Top