Referencing columns in a query

[riggsd]

I know that on a form I can refer to a specific column of a multi-column combo/list box and then use that information in a query, but how do I refer to a specific column of a lookup field that's in a table and use that in a query?

TIA

 

[Jon K]

You can use the DLookup() function.

 

[riggsd]

But doesn't DLookup only find a unique value in a column?

I need to find multiple values in a column that contains twolookup columns but the data is in column 2 of the lookup column.

Here's my query:

WHERE ((([tbl_PPLInstances].[FirstFlight]) Between [Forms]![frm_SelectbyFlightRange]![txtFirstSortID] And [Forms]![frm_SelectbyFlightRange]![txtLastSortID]) AND (([tblPPLInstances].[LastFlight])>=[Forms]![frm_SelectbyFlightRange]![txtMidSortID]))

The [tbl_PPLInstances].[FirstFlight] and [tblPPLInstances].[LastFlight] fields are lookup fields, with two columns each, column 1 being the flight id and column 2 being the sort id.

How would I use DLookup (or some other method) to tell the query to get column 2 of the FirstFlight or LastFlight Lookup cell?

 

[Rich]

The easy answer is to add an unbound hidden text box and set its controls source to =[MyCombo].[Column](1) and reference that textbox in the criteria, or use the Eval function

column 1 is actually the second column, they start at 0

 

[riggsd]

That's what I've done for the sort id on the form since the user actually chooses the flight. The unbound text field then holds the sort id used in the query.

That sort id is what the query needs to go look in the table for but I can't have a text field on the form holding information from the table since the table isn't loaded on the form.