Lookup returning wrong value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Access 2000

I have two tables, a main table and a lookup table

The key in the lookup table is an autonumber field, which starts at one

In the main table, I am trying to make a field lookup a combo box which refers to the second table

When I use the combo box and select the first value, it shows a 1, but if I try set up a query on the table to show only the 1's, it does not work. I have to query for 0's in that field. If I change the field lookup property to a text box, it does indeed show a 0 instead of a 1. If a 2 was selected in the combo box, a 1 is shown in a text box.

I don't think it was like this in '97. Is this really the way it works in Access 2000? Does it do it for anyone else, or is there something going on with just me

Thanks
 
Aft

You have encountered one of the many headaches folks find in trying to use
the "lookup" data field type. While well-intentioned, by storing something
different than it displays, this data type causes much confusion. You are
better off using a Long Numeric data type (you did say your "lookup" table
used Autonumbers, right?) in your main table.

You are also much better off not working directly in the tables. If you
created a form, you could use a combo box that: 1) used the lookup table
for row source, and 2) stored the ID, as a foreign key, in the main table.
 
Back
Top