Sorry you feel that way. The unfortunate fact is that what is displayed in
datasheet view of a query is not what is stored if you are using a field that
uses the lookup feature. That's why most of us avoid using the lookup field.

On forms you can use a combobox to display one value while actually storing
another value. So, I can have a combobox that stores a primary key from my
personnel table in a foreign key field of another table that lists family
members. That is proper use of a relational database.

The simple way to solve the problem would be to change your subquery so it
references the table that really contains the value you are looking for.
WIthout more detail, I would say the sub-query might look something like the
following.

SELECT T.TheSerialNumberField
FROM INSTALL INNER JOIN TheSourceTableForTheLookup as T
ON INSTALL.Serial_Number = T.SomeRelatedField

I was going to attempt to download your sample db, but for some reason I got a
404 error.

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

Farkyss wrote:
The 'lookup junk' was put in by Access itself with it's wizard. The
implementation of the form is -supposed- to be independant of the underlying
storage, otherwise it makes a mockery of the SQL standard. I will be making
effort in the future to completely avoid Access - it's a load of trash

"PieterLinden via AccessMonster.com" wrote:

Farkyss wrote:
Correct behaviour can be restored by changing the SERIAL_NUMBER in the
INSTALL table to be a plain text box rather than a LOOKUP table of values
from the PORD table. Upon changing, the values stored in the SERIAL_NUMBER
field suddenly change to some unrelated digits.

Since this behaviour is clearly incorrect, could someone with a MVP tag
please report it as a bug? SQL queries on a field should be returning the
field's value, not a reference number.

As for my db? I'll have to go with a textbox. It isn't the functionality it
SHOULD have been, but it'll do

Sounds like the problem is the lookup junk in your table. Another reason to
avoid them.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201003/1

.