NOT criteria in query not working

[jwr]

Following is the SQL from a query where I have inserted in the criteria
section of a field of the query (Dealer Name) Not "Skyland Equipment".
However, the name still pulls forward when the query is run. The spelling
is correct. Am I typing it correctly? There is no problem in running the
query, but I do not want this particular name to be in the query.


SELECT Customers.ControlNum, Orders.OrderDate, [Quantity]*[UnitPrice] AS [PO
Amount], [PO Amount]*0.95 AS [95%], [PO Amount]*0.05 AS [5%],
Dealer.DealerName
FROM (Dealer RIGHT JOIN (Customers INNER JOIN Orders ON Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN [Order
Details] ON Orders.OrderID = [Order Details].OrderID
WHERE (((Orders.OrderDate)>=[forms]![Report Date Range]![Beginning Order
Date] And (Orders.OrderDate)<=[forms]![Report Date Range]![Ending Order
Date]) AND (([Quantity]*[UnitPrice])>0) AND (Not
(Dealer.DealerName)="Skyland Equipment"))
ORDER BY Orders.OrderDate;


Thanks in advance

 

[Ken Snell \(MVP\)]

Change

AND (Not
(Dealer.DealerName)="Skyland Equipment"))

to this:

AND (
(Dealer.DealerName)<>"Skyland Equipment"))

 

[jwr]

I changed as below. Still get Skyland Equipment in query.

SELECT Customers.ControlNum, Orders.OrderDate, [Quantity]*[UnitPrice] AS [PO
Amount], [PO Amount]*0.95 AS [95%], [PO Amount]*0.05 AS [5%],
Dealer.DealerName
FROM (Dealer RIGHT JOIN (Customers INNER JOIN Orders ON Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN [Order
Details] ON Orders.OrderID = [Order Details].OrderID
WHERE (((Orders.OrderDate)>=[forms]![Report Date Range]![Beginning Order
Date] And (Orders.OrderDate)<=[forms]![Report Date Range]![Ending Order
Date]) AND (([Quantity]*[UnitPrice])>0) AND ((Dealer.DealerName)<>"Skyland
Equipment"))
ORDER BY Orders.OrderDate;

Change

AND (Not
(Dealer.DealerName)="Skyland Equipment"))

to this:

AND (
(Dealer.DealerName)<>"Skyland Equipment"))

--

Ken Snell
<MS ACCESS MVP>

Following is the SQL from a query where I have inserted in the criteria
section of a field of the query (Dealer Name) Not "Skyland Equipment".
However, the name still pulls forward when the query is run. The spelling
is correct. Am I typing it correctly? There is no problem in running the
query, but I do not want this particular name to be in the query.


SELECT Customers.ControlNum, Orders.OrderDate, [Quantity]*[UnitPrice] AS
[PO
Amount], [PO Amount]*0.95 AS [95%], [PO Amount]*0.05 AS [5%],
Dealer.DealerName
FROM (Dealer RIGHT JOIN (Customers INNER JOIN Orders ON
Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
[Order
Details] ON Orders.OrderID = [Order Details].OrderID
WHERE (((Orders.OrderDate)>=[forms]![Report Date Range]![Beginning Order
Date] And (Orders.OrderDate)<=[forms]![Report Date Range]![Ending Order
Date]) AND (([Quantity]*[UnitPrice])>0) AND (Not
(Dealer.DealerName)="Skyland Equipment"))
ORDER BY Orders.OrderDate;


Thanks in advance

 

[Ken Snell \(MVP\)]

Check that you've spelled Skyland Equipment exactly the same in the query as
it is spelled in the table's records. Test this by creating a very simple
query on Dealer table and trying to filter out the Skyland Equipment name.

--

Ken Snell
<MS ACCESS MVP>

I changed as below. Still get Skyland Equipment in query.

SELECT Customers.ControlNum, Orders.OrderDate, [Quantity]*[UnitPrice] AS
[PO
Amount], [PO Amount]*0.95 AS [95%], [PO Amount]*0.05 AS [5%],
Dealer.DealerName
FROM (Dealer RIGHT JOIN (Customers INNER JOIN Orders ON
Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
[Order
Details] ON Orders.OrderID = [Order Details].OrderID
WHERE (((Orders.OrderDate)>=[forms]![Report Date Range]![Beginning Order
Date] And (Orders.OrderDate)<=[forms]![Report Date Range]![Ending Order
Date]) AND (([Quantity]*[UnitPrice])>0) AND ((Dealer.DealerName)<>"Skyland
Equipment"))
ORDER BY Orders.OrderDate;

Change

AND (Not
(Dealer.DealerName)="Skyland Equipment"))

to this:

AND (
(Dealer.DealerName)<>"Skyland Equipment"))

--

Ken Snell
<MS ACCESS MVP>

Following is the SQL from a query where I have inserted in the criteria
section of a field of the query (Dealer Name) Not "Skyland Equipment".
However, the name still pulls forward when the query is run. The spelling
is correct. Am I typing it correctly? There is no problem in running the
query, but I do not want this particular name to be in the query.


SELECT Customers.ControlNum, Orders.OrderDate, [Quantity]*[UnitPrice]
AS
[PO
Amount], [PO Amount]*0.95 AS [95%], [PO Amount]*0.05 AS [5%],
Dealer.DealerName
FROM (Dealer RIGHT JOIN (Customers INNER JOIN Orders ON
Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
[Order
Details] ON Orders.OrderID = [Order Details].OrderID
WHERE (((Orders.OrderDate)>=[forms]![Report Date Range]![Beginning
Order
Date] And (Orders.OrderDate)<=[forms]![Report Date Range]![Ending Order
Date]) AND (([Quantity]*[UnitPrice])>0) AND (Not
(Dealer.DealerName)="Skyland Equipment"))
ORDER BY Orders.OrderDate;


Thanks in advance

 

[jwr]

I finally got it to work by using the following in my query criteria:
Not "Sky*"
Thanks for your help

Check that you've spelled Skyland Equipment exactly the same in the query as
it is spelled in the table's records. Test this by creating a very simple
query on Dealer table and trying to filter out the Skyland Equipment name.

--

Ken Snell
<MS ACCESS MVP>

I changed as below. Still get Skyland Equipment in query.

SELECT Customers.ControlNum, Orders.OrderDate, [Quantity]*[UnitPrice] AS
[PO
Amount], [PO Amount]*0.95 AS [95%], [PO Amount]*0.05 AS [5%],
Dealer.DealerName
FROM (Dealer RIGHT JOIN (Customers INNER JOIN Orders ON
Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
[Order
Details] ON Orders.OrderID = [Order Details].OrderID
WHERE (((Orders.OrderDate)>=[forms]![Report Date Range]![Beginning Order
Date] And (Orders.OrderDate)<=[forms]![Report Date Range]![Ending Order
Date]) AND (([Quantity]*[UnitPrice])>0) AND

Equipment"))
ORDER BY Orders.OrderDate;

Change

AND (Not
(Dealer.DealerName)="Skyland Equipment"))

to this:

AND (
(Dealer.DealerName)<>"Skyland Equipment"))

--

Ken Snell
<MS ACCESS MVP>

Following is the SQL from a query where I have inserted in the criteria
section of a field of the query (Dealer Name) Not "Skyland Equipment".
However, the name still pulls forward when the query is run. The spelling
is correct. Am I typing it correctly? There is no problem in

running
the

query, but I do not want this particular name to be in the query.


SELECT Customers.ControlNum, Orders.OrderDate, [Quantity]*[UnitPrice]
AS
[PO
Amount], [PO Amount]*0.95 AS [95%], [PO Amount]*0.05 AS [5%],
Dealer.DealerName
FROM (Dealer RIGHT JOIN (Customers INNER JOIN Orders ON
Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
[Order
Details] ON Orders.OrderID = [Order Details].OrderID
WHERE (((Orders.OrderDate)>=[forms]![Report Date Range]![Beginning
Order
Date] And (Orders.OrderDate)<=[forms]![Report Date Range]![Ending Order
Date]) AND (([Quantity]*[UnitPrice])>0) AND (Not
(Dealer.DealerName)="Skyland Equipment"))
ORDER BY Orders.OrderDate;


Thanks in advance

 

[John Vinson]

I finally got it to work by using the following in my query criteria:
Not "Sky*"

That's very odd, since unless you use the LIKE operator, wildcards are
ignored. I would expect this to retrieve all records except those
containing the literal four-character text string Sky* in this field.

John W. Vinson[MVP]