HOW TO USE NOT LIKE EXPRESSION

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How to not select certain multiple criteria. I want to not select certain
contractor codes. I tried the not like function but can't get it to work on
not selecting multiple contractors. I tried to setup the formula like this:
Not like "Vic" or not like "Pioneer" or not like "Bud" but this does not
work. How do I setup an expression to not select VIC, PIONEER OR BUD (AND
MANY OTHER CONTRACTORS)?

Thanks
 
"Vic" NOT LIKE "Vic" OR "Vic" NOT LIKE "pioneer"

evaluates to

False OR true

which is

true


You should use AND, instead of OR.


Alternatively:

"Vic" NOT IN( "Vic", "Pioneer", "Bud" )



Vanderghast, Access MVP
 
How to not select certain multiple criteria. I want to not select certain
contractor codes. I tried the not like function but can't get it to work on
not selecting multiple contractors. I tried to setup the formula like this:
Not like "Vic" or not like "Pioneer" or not like "Bud" but this does not
work. How do I setup an expression to not select VIC, PIONEER OR BUD (AND
MANY OTHER CONTRACTORS)?

Thanks
Click to expand...

The LIKE operator is used *only* for searches using wildcards, to find partial
matches. If you don't have a * or # or ? or other wildcard character in the
criterion, then LIKE is functionally identical to =. In this case you don't
need LIKE at all!

Instead, use a criterion of

NOT IN ("Vic", "Pioneer", "Bud")

If the list of contractors to be excluded is extensive and/or subject to
change, then I'd suggest setting up an Excludes table with one row per
contractor to be excluded, and use the Unmatched Query Wizard to show all
records except those in the Excludes table.

John W. Vinson [MVP]
 
Give us an idea of what kind of data is normally found in this field...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Try: Not In ('VIC', PIONEER','BUD')

HTH

Pieter

Jeff Boyce said:
Give us an idea of what kind of data is normally found in this field...

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
"Vic" NOT IN( "Vic", "Pioneer", "Bud" )
Click to expand...
From an implementation point of view, the OP could have a SQL
Click to expand...
PROCEDURE (parameter Query) with many parameters that have a default
value of NULL e.g. (ANSI-92 Query Mode):

CREATE PROCEDURE GetOrdersExcludeCustomers
(
arg_exclude_customerID_01 CHAR(5) = NULL,
arg_exclude_customerID_02 CHAR(5) = NULL,
arg_exclude_customerID_03 CHAR(5) = NULL,
arg_exclude_customerID_04 CHAR(5) = NULL,
arg_exclude_customerID_05 CHAR(5) = NULL,
arg_exclude_customerID_06 CHAR(5) = NULL,
arg_exclude_customerID_07 CHAR(5) = NULL,
arg_exclude_customerID_08 CHAR(5) = NULL,
arg_exclude_customerID_09 CHAR(5) = NULL,
arg_exclude_customerID_10 CHAR(5) = NULL,
arg_exclude_customerID_11 CHAR(5) = NULL,
arg_exclude_customerID_12 CHAR(5) = NULL,
arg_exclude_customerID_13 CHAR(5) = NULL,
arg_exclude_customerID_14 CHAR(5) = NULL,
arg_exclude_customerID_15 CHAR(5) = NULL
)
AS
SELECT CustomerID, OrderID, OrderDate
FROM Orders
WHERE CustomerID NOT IN (arg_exclude_customerID_01,
arg_exclude_customerID_02, arg_exclude_customerID_03,
arg_exclude_customerID_04, arg_exclude_customerID_05,
arg_exclude_customerID_06, arg_exclude_customerID_07,
arg_exclude_customerID_08, arg_exclude_customerID_09,
arg_exclude_customerID_10, arg_exclude_customerID_11,
arg_exclude_customerID_12, arg_exclude_customerID_13,
arg_exclude_customerID_14, arg_exclude_customerID_15);

The number of parameters in a proc is theoretically unlimited but the
practical upper limit, due to "query too complex" errors and the like,
is still quite high (approx two thousand). The Jet SQL Help (Access)
suggests the limit is 255: is this the officially supported limit or
merely another error with this document?

Jamie.

--
 
CREATE PROCEDURE GetOrdersExcludeCustomers
(
arg_exclude_customerID_01 CHAR(5) = NULL,
arg_exclude_customerID_02 CHAR(5) = NULL,
arg_exclude_customerID_03 CHAR(5) = NULL,
arg_exclude_customerID_04 CHAR(5) = NULL,
arg_exclude_customerID_05 CHAR(5) = NULL,
arg_exclude_customerID_06 CHAR(5) = NULL,
arg_exclude_customerID_07 CHAR(5) = NULL,
arg_exclude_customerID_08 CHAR(5) = NULL,
arg_exclude_customerID_09 CHAR(5) = NULL,
arg_exclude_customerID_10 CHAR(5) = NULL,
arg_exclude_customerID_11 CHAR(5) = NULL,
arg_exclude_customerID_12 CHAR(5) = NULL,
arg_exclude_customerID_13 CHAR(5) = NULL,
arg_exclude_customerID_14 CHAR(5) = NULL,
arg_exclude_customerID_15 CHAR(5) = NULL
)
AS
SELECT CustomerID, OrderID, OrderDate
FROM Orders
WHERE CustomerID NOT IN (arg_exclude_customerID_01,
arg_exclude_customerID_02, arg_exclude_customerID_03,
arg_exclude_customerID_04, arg_exclude_customerID_05,
arg_exclude_customerID_06, arg_exclude_customerID_07,
arg_exclude_customerID_08, arg_exclude_customerID_09,
arg_exclude_customerID_10, arg_exclude_customerID_11,
arg_exclude_customerID_12, arg_exclude_customerID_13,
arg_exclude_customerID_14, arg_exclude_customerID_15);
Click to expand...

Example for Northwind. Usage e.g. (ANSI-92 Query Mode):

EXECUTE GetOrdersExcludeCustomers 'SAVEA', 'ANTON', 'HUNGO';

Jamie

--
 
Here *another* Jet different behavior than what the Standard requires.

If you try

? eval( " 4 NOT IN (1, 2, null) ")

-1

where -1 is to be read as "true" ( 0 being read as false). That is fine?
Well, that is not what the Standard requires, as long as I remember, since
the Standard will require the expression to evaluate to null, since

4 NOT IN (1, 2, null)
== 4 <> 1 AND 4 <> 2 AND 4 <> null
== true AND true AND null
== null


But that is NOT ALL the story...

On the other hand, Jet behaves that way ONLY FOR CONSTANT list, as
illustrated. Jet behaves like the Standard if the 'list" comes from a query:

.... WHERE 4 NOT IN (SELECT ... )

In that case, if the SELECT contains a NULL, the result of the NOT IN is
also NULL !


So, basically, yes, in Jet, that solution will work, but would it work with
MS SQL Server used as database engine? Note that I have NOT explicitly
checked that precise particular case, though.


Vanderghast, Access MVP
 
basically, yes, in Jet, that solution will work, but would it work with
MS SQL Server used as database engine? Note that I have NOT explicitly
checked that precise particular case, though.
Click to expand...

You are correct, of course. I could post a SQL Server equivalent using
COALESCE to handle the NULL value but someone would point out it isn't
compliant with SQL/PSM ;-)

Jamie.

--
 
Back
Top