SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789";

produces expected results whereas

SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789";

procudes an unexpected, empty result. FYI, the lookup for this field in the
INSTALL is

SELECT [PORD].[PORD_ID], PORD.[SERIAL_NUMBER] FROM PORD ORDER BY
[SERIAL_NUMBER];

have also tried replacing that with this
SELECT PORD.[SERIAL_NUMBER] FROM PORD ORDER BY [SERIAL_NUMBER];
as the original lookup was made by access's 'insert a field from another
table' wizard
This produces the same incorrect results

"John Spencer" wrote:

So using TRIM on both Serial_Number fields does not work. THe following fails

SELECT po.PURCHASE_DATE
, pd.QTY
, pd.SERIAL_NUMBER
, pa.PART_ID
, pa.DESCRIPTION
FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd
ON po.SALES_NUMBER=pd.SALES_NUMBER)
INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID
WHERE Trim(pd.SERIAL_NUMBER) NOT IN
(SELECT Trim(SERIAL_NUMBER)
FROM INSTALL WHERE
SERIAL_NUMBER IS NOT NULL);

As a check have you tried something like the following to make sure the record
in the INSTALL table actually contains the value 999789.
SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER = '999789'

An alternative query:
SELECT po.PURCHASE_DATE
, pd.QTY
, pd.SERIAL_NUMBER
, pa.PART_ID
, pa.DESCRIPTION
FROM ((PURCHASEORDER AS po
INNER JOIN PORD AS pd
ON po.SALES_NUMBER=pd.SALES_NUMBER)
INNER JOIN PART AS pa
ON pd.PART_ID=pa.PART_ID)
LEFT JOIN INSTALL as I
ON Pd.SERIAL_Number = I.Serial_Number
WHERE I.SerialNumber Is Null

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Farkyss wrote:
Updating this question with a refresh:

http://www.clanimperium.net/1.JPG
http://www.clanimperium.net/2.JPG
http://www.clanimperium.net/3.JPG
http://www.clanimperium.net/4.JPG
(capital JPG is important)

1 shows results of
SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION
FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON
po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON
pd.PART_ID=pa.PART_ID;
(no WHERE clause at all - results as expected)

2 shows results of the subquery on it's own
SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER IS NOT NULL;
(results as expected)

3 shows results of
SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION
FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON
po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON
pd.PART_ID=pa.PART_ID;
WHERE pd.SERIAL_NUMBER NOT IN ('999789');
(manual NOT IN clause - results as expected - 999789 excluded)

4 Shows results of the full query
SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION
FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON
po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON
pd.PART_ID=pa.PART_ID
WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL WHERE
SERIAL_NUMBER IS NOT NULL);
(Results NOT as expected 999789 isn't excluded)


TRIMming the subquery doesn't help. VAL can't be used because serial_number
can be alphabetic. Essentially NOT IN doesn't work with a list generated from
a subquery. Could someone attempt to reproduce these results? Any comment
from Microsoft from official? Anyone with an alternative method? This whole
project is frozen until this is fixed.
.