Did your values in the INSTALL table come from PORD via a lookup table?
That's the issue as I see it.

"orange via AccessMonster.com" wrote:

Farkyss wrote:
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

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

[quoted text clipped - 77 lines]
project is frozen until this is fixed.
.
Farkyss,

I was having problems with some of the queries then I realized I had a typing
error. I was using 999789 in Install and 999879 in PORD. I'm using Acc 2003
on XP SP3.
Once I adjusted that an now am getting expected results using John Spencer's
query (orig)
Here are images from my tests:

My test files http://www.box.net/files#/files/0/f/.../1/f_397107264
John's Query SQL
http://www.box.net/files#/files/0/f/.../1/f_397107274
Result of John's query
http://www.box.net/files#/files/0/f/.../1/f_397107256

Also, I am able to run successfully
SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789";

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

--
Message posted via http://www.accessmonster.com

.