HOW TO USE NOT LIKE EXPRESSION

[Bjorgoal]

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

[Michel Walsh]

"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

"Bjorgoal" <Bjor...@discussions.microsoft.com> wrote in message
news:C1909998-A5F7-43C8...@microsoft.com...

[John W. Vinson]

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]

[Jeff Boyce]

Give us an idea of what kind of data is normally found in this field...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Bjorgoal" <Bjor...@discussions.microsoft.com> wrote in message
news:C1909998-A5F7-43C8...@microsoft.com...

[Pieter Wijnen]

Try: Not In ('VIC', PIONEER','BUD')

HTH

Pieter

"Jeff Boyce" <nons...@nonsense.com> wrote in message
news:OI%23nvxVA...@TK2MSFTNGP04.phx.gbl...

[Jamie Collins]

On Sep 27, 8:24 pm, "Michel Walsh"

<vanderghast@VirusAreFunnierThanSpam> wrote:
> "Vic" NOT IN( "Vic", "Pioneer", "Bud" )

>From an implementation point of view, the OP could have a SQL
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.

--

[Jamie Collins]

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

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

Jamie

--

[Michel Walsh]

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

"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:1191242360....@n39g2000hsh.googlegroups.com...

[Jamie Collins]

On Oct 1, 2:06 pm, "Michel Walsh"

<vanderghast@VirusAreFunnierThanSpam> wrote:
> 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.

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.

--