specifying multiple "not like" criteria

[SageOne]

I have a table with muliple columns of data. In want to select all the data
with the exception of certain records that meet two criteria. I would like to
filter out all records in my table that simultaneouly have a spefic year in
one column "year = 2007" and a specific code in the another column "code =
volmkfrz". Please see my SQL below. Instead of filtering out all records
that meet both criteria, it is filtering out all records where year = 2007
regardless of code and all records with the code = volmkfrz regardless of
year. Please advise. All help is greatly appreciated.


SELECT [new holds 091508].Identifier, [new holds 091508].[Year Eff date],
[new holds 091508].BrokerId, [new holds 091508].BrokerName, [new holds
091508].IDXMemberId, [new holds 091508].CustomerName, [new holds
091508].HoldReasonCd, [new holds 091508].AmountCommission, [new holds
091508].MemberStateCd, [new holds 091508].TypeOfBusinessName, [new holds
091508].VendorTaxName, [new holds 091508].VendorId, [new holds
091508].CustomerId, [new holds 091508].DateEarned
FROM [new holds 091508]
WHERE ((([new holds 091508].[Year Eff date]) Not Like 2007) AND (([new holds
091508].HoldReasonCd) Not Like "volmkfrz"));

 

[Allen Browne]

Try:
WHERE ([new holds 091508].[Year Eff date] <> 2007)
OR ([new holds 091508].HoldReasonCd <> "volmkfrz")

Records where these fields are null will still be eliminated, so you might
want:
WHERE ([new holds 091508].[Year Eff date] <> 2007)
OR ([new holds 091508].[Year Eff date] Is Null)
OR ([new holds 091508].HoldReasonCd <> "volmkfrz")
OR ([new holds 091508].HoldReasonCd Is Null)

 

[Lord Kelvan]

when using negitive logic you have to use a negitive constraint

if you say like and like the negitive equlivant is not like or not
like

i know that surface logic demands that that is incorrect but
mathmatically it is correct.

so when you say not like and not like it is basically a or

try

SELECT [new holds 091508].Identifier, [new holds 091508].[Year Eff
date],
[new holds 091508].BrokerId, [new holds 091508].BrokerName, [new
holds
091508].IDXMemberId, [new holds 091508].CustomerName, [new holds
091508].HoldReasonCd, [new holds 091508].AmountCommission, [new holds
091508].MemberStateCd, [new holds 091508].TypeOfBusinessName, [new
holds
091508].VendorTaxName, [new holds 091508].VendorId, [new holds
091508].CustomerId, [new holds 091508].DateEarned
FROM [new holds 091508]
WHERE ((([new holds 091508].[Year Eff date]) Not Like 2007) OR (([new
holds
091508].HoldReasonCd) Not Like "volmkfrz"));

 

[John W. Vinson]

WHERE ((([new holds 091508].[Year Eff date]) Not Like 2007) AND (([new holds
091508].HoldReasonCd) Not Like "volmkfrz"));

Just to add to Allen Browne's and Lord Kalvan's suggestions - it's not
necessary or appropriate to use LIKE here. The LIKE operator without wildcards
(and you aren't using wildcards!) is identical to the default = operator. The
only purpose of LIKE is to allow partial matches, such as

LIKE "volm*"

to find all values beginning with volm. It makes no sense to use LIKE with a
numeric field.

 

[SageOne]

Is there an documentation written on using multiple "not like" criteria?

WHERE ((([new holds 091508].[Year Eff date]) Not Like 2007) AND (([new holds
091508].HoldReasonCd) Not Like "volmkfrz"));

Just to add to Allen Browne's and Lord Kalvan's suggestions - it's not
necessary or appropriate to use LIKE here. The LIKE operator without wildcards
(and you aren't using wildcards!) is identical to the default = operator. The
only purpose of LIKE is to allow partial matches, such as

LIKE "volm*"

to find all values beginning with volm. It makes no sense to use LIKE with a
numeric field.

 

[Lord Kelvan]

no it is just programming logic if you would say

like and like
or
= and =

then the negitive logic is

not like or not like
or
<> or <>

and same if it was like or like would be not like and not like

as john said the use of like is for wild cards but there is no reason
you cannot use it without wildcard as you seem to realise.

hope this helps

Regards
Kelvan

 

[John W. Vinson]

Is there an documentation written on using multiple "not like" criteria?

Well, you could dig stuff out I suppose... but you have not answered my
objection. WHY are you using *ANY* "Like" criteria at all? None of your
examples need the LIKE operator.

All you need to do in this case is change the AND to OR. You say you want to
retrieve the record if the year is not equal to 2007; *or* if the hold reason
is not equal to vomkfrz. If that is an accurate description of the process
then you can use

SELECT [new holds 091508].Identifier, [new holds 091508].[Year Eff date],
[new holds 091508].BrokerId, [new holds 091508].BrokerName, [new holds
091508].IDXMemberId, [new holds 091508].CustomerName, [new holds
091508].HoldReasonCd, [new holds 091508].AmountCommission, [new holds
091508].MemberStateCd, [new holds 091508].TypeOfBusinessName, [new holds
091508].VendorTaxName, [new holds 091508].VendorId, [new holds
091508].CustomerId, [new holds 091508].DateEarned
FROM [new holds 091508]
WHERE ((([new holds 091508].[Year Eff date]) <> 2007) OR (([new holds
091508].HoldReasonCd) <> "volmkfrz"));

Equivalently, you can negate the entire criterion:

SELECT [new holds 091508].Identifier, [new holds 091508].[Year Eff date],
[new holds 091508].BrokerId, [new holds 091508].BrokerName, [new holds
091508].IDXMemberId, [new holds 091508].CustomerName, [new holds
091508].HoldReasonCd, [new holds 091508].AmountCommission, [new holds
091508].MemberStateCd, [new holds 091508].TypeOfBusinessName, [new holds
091508].VendorTaxName, [new holds 091508].VendorId, [new holds
091508].CustomerId, [new holds 091508].DateEarned
FROM [new holds 091508]
WHERE NOT ((([new holds 091508].[Year Eff date]) = 2007) OR (([new holds
091508].HoldReasonCd) = "volmkfrz")));

This will identify all the records where either of the conditions you want to
exclude is true, and specifically exclude just those records.