2 condition

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.BillOfL ading) 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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"mavis" wrote in message
...
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.BillOfL ading) 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"));

HiAllen,

Thanks! It works great.


"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"mavis" wrote in message
...
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.BillOfL ading) 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"));