Lookup returning wrong value

A

Art

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
 
J

Jeff Boyce

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top