Specifying lookup column to use in a query

[riggsd]

I know that in a form, I can tell a field to fill in the data from the second column of a lookup combo box, using table_name.field_name(1), but how do I use this in a query? By default the query extracts the first column which is the lookup id but I need it to select the second column which holds the data I need.

Is this possible?

 

[Uncle Gizmo]

Should you not just check or uncheck the "show" check box? or have I got the wrong end of the stick?

 

[riggsd]

Yeah, I think you do. The show checkbox isn't used for it.

In the lookup combo box, it looks up the value from another table.

The second table has a ID number field and a data field.

In the lookup combo box, both columns from the second column are there, but the first column is set to 0" so it doesn't show up, just column 2, but it is actually there in the lookup combo box.

When the query runs it automatically picks up the ID number in the first column which is why the number is added to a new table or exported to Excel, but if I could tell the query to ignore column 1 in the lookup box and use column 2, then it would export the data instead of the ID number.

 

[Uncle Gizmo]

I was just going to post a link to a previous thread, but I see that the answer I was going to suggest is one I have already posted to a thread by yourself!

I was wondering if you would be able to post a cut down version of your DB as I cannot see that what you want to do is not possible.

 

[Milothicus]

I'm having the same problems. here's my description of it:

on my 'search psage', i have a combobox (cbokwsrch) that i'm using to choose a range of values for a field (kw) in my table (input). the combo is liked to a table (tblKw) that has an autonumber index (column 0), and a column that lists the separation points between my ranges (column 1: 250, 500, 750, 1000, 1500, 2000). what i want in my search query criterion is something like:

between cbokwsrch(selected value -1) and cbokwsrch(selected value)

so if I choose 750 in the combobox, the search will return:

between 500 and 750.

if i choose 1500, it should return

between 1000 and 1500.

To do this, my combo is bound to column 0 (the autonumber), so i want:

between cbokwsrch.column(1, cbokwsrch-1) and cbokwsrch(1, cbokwsrch)

but in the build dialog for the criterion, i can't use the column property, and my ranges aren't consistent, so i can't bind the combo to column 1 and search:

between cbokwsrch and (cbokwsrch-500)

I hope this is clear, and i hope it's the same problem riggsd is having...

 

[Milothicus]

ahem....bump