Query on a lookup field fails

  • Thread starter Thread starter Sunman88
  • Start date Start date
S

Sunman88

I'm trying to run a simple query on a table with a lookup
field. The lookup field is a long integer and it looks up
a text field in a master table but the query does not
return any rows. Any ideas how to fix this. Here's the
query:

select price from orders where orders.[vendornum]=10;

Vendornum is a long integer.
The vendornum field is the lookup field. The table has
many records with this vendornum.
 
Chances are you're expecting to find the text values in the "lookup" field's
values...but that is not what is stored in that field! The lookup field
option in tables is nice to look at, but creates major havoc when trying to
run queries etc. and you forget that what is in the field isn't what you
see.

Can you give us a bit more info about the entire table's setup ? Then we can
give more specific info about how to get the query to work as you desire.
 
The table has 3 fields: VendorNum (which is a long integer
and is the combo Box), Quantity (which is an integer) and
Price which is a currency field. The VendorNum lookups up
the VendorName in the Vendors table. The Vendors table has
a field VendorNum which is an AutoNum. And here's the
query against the first table (orders):

select price where vendorNum=15

-----Original Message-----
Chances are you're expecting to find the text values in the "lookup" field's
values...but that is not what is stored in that field! The lookup field
option in tables is nice to look at, but creates major havoc when trying to
run queries etc. and you forget that what is in the field isn't what you
see.

Can you give us a bit more info about the entire table's setup ? Then we can
give more specific info about how to get the query to work as you desire.

--
Ken Snell
<MS ACCESS MVP>

I'm trying to run a simple query on a table with a lookup
field. The lookup field is a long integer and it looks up
a text field in a master table but the query does not
return any rows. Any ideas how to fix this. Here's the
query:

select price from orders where orders.[vendornum]=10;

Vendornum is a long integer.
The vendornum field is the lookup field. The table has
many records with this vendornum.
Click to expand...


.
Click to expand...
 
Your query statement is incomplete. You need to include the table name in
it:

select price from tablename where vendorNum=15
--
Ken Snell
<MS ACCESS MVP>

Ed Hamila said:
The table has 3 fields: VendorNum (which is a long integer
and is the combo Box), Quantity (which is an integer) and
Price which is a currency field. The VendorNum lookups up
the VendorName in the Vendors table. The Vendors table has
a field VendorNum which is an AutoNum. And here's the
query against the first table (orders):

select price where vendorNum=15

-----Original Message-----
Chances are you're expecting to find the text values in the "lookup" field's
values...but that is not what is stored in that field! The lookup field
option in tables is nice to look at, but creates major havoc when trying to
run queries etc. and you forget that what is in the field isn't what you
see.

Can you give us a bit more info about the entire table's setup ? Then we can
give more specific info about how to get the query to work as you desire.

--
Ken Snell
<MS ACCESS MVP>

I'm trying to run a simple query on a table with a lookup
field. The lookup field is a long integer and it looks up
a text field in a master table but the query does not
return any rows. Any ideas how to fix this. Here's the
query:

select price from orders where orders.[vendornum]=10;

Vendornum is a long integer.
The vendornum field is the lookup field. The table has
many records with this vendornum.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top