newbie question: lookup always returns ID, not combo box value

[Sir Benjamin Nunn]

Hi,

I'm not very good with Access (using 2003) and can't get round a particular
problem.

I use the lookup wizard to put a combo box lookup, to allow the text field
in one table to be picked from the list of values in another table.

However, every time I run a query to display values from the table with the
lookup, it returns the idea of the value I've selected, and not the actual
value.

I've simplified the problem below to explain it.


Table 1: ListOfFish

Column 1: ID - Int
Column 2: FishName - Text


1 Cod
2 Trout


Table 2: CaughtFish

Column 1: ID - Int
Column 2: Date - Date
Column 3: Fish - Lookup ListOfFish.FishName


1 01/01/2001 <Cod>
2 02/02/2002 <Cod>
3 03/03/2005 <Trout>


When I run a query to return the 'Fish' for 02/02/2002, I would expect it to
return 'Cod', but instead it returns '1'.

Where am I going wrong?

Any and all help much appreciated, thanks.

BTN

 

[Duane Hookom]

Normally you want to store the ID fields while displaying the "name" field
in the combo box. This is done by setting the column widths property.
Setting the property to something like:
Column Widths: 0",1",1.5"
will hide the first column.

 

[Sir Benjamin Nunn]

Normally you want to store the ID fields while displaying the "name" field
in the combo box. This is done by setting the column widths property.
Setting the property to something like:
Column Widths: 0",1",1.5"
will hide the first column.

Column Widths are aleady similar to that. To clarify, I don't want my
queries to return the ID, I want the names - e.g. the same values I select
in the combo box.

BTN

 

[Duane Hookom]

Your query must contain the lookup table if you want to display a field from
the lookup table.

 

[Ron2006]

In your query wizard include the lookup table and link the ID of that
table to the field in your main table that the ID number is showing up
in. And then drag and drop or double click the discription/name in the
lookup table so that it is what is on the resultant query.

Ron