Show field column count in a query

[Christine Pearc]

In forms, I frequenty use the following expression to get the results needed from a field's column:
=EmployeeID.column(1).

However, when I try to use the column function in a query it doesn't like it:
EmployeeName:[EmployeeID].Column(1)

What am I doing something wrong?

Along the same line, if I want to use =EmployeeID.column(1) for an unbound control in a form, why must you put the bound field on the form just to get the info? In this case, I want to do this to display the employee's name - not the primary key - without the scroll bar. Every time I do this, Access performance analyser tells me to use fewer controls, but you can't if you have to have the reference. The same thing seems to be required in queries, i.e. include the unaltered control to get an expression.

Help and advise, as always, will be appreciated!
Christine

 

[Mile-O]

Try:

[EmployeeID].[Column](1)

 

[The_Doc_Man]

Tech check...

However, when I try to use the column function in a query it doesn't like it:

No DUH there! Column is not really a function, it is an indexable collection. Further, it is not a collection in context of anything but a multi-column combo box or list box - a query has no inherent columns that I've ever seen.

On a form with a list box or combo box, .Column should work fine. Any other place - as is often said on gangsta TV ... fahgeddaboudit.

 

[Christine Pearc]

I tried SJ's suggestion -- [EmployeeID].[Column](1) -- but that doesn't work. So, if I understand The Doc Man correctly, it is not possible to show anything but a combo/lookup field's first column in a query; the only time this is possible is in a form or report. That is a shame. Would you let me know if I've misunderstood?

Could either one of you (or someone else) help with the 2nd part of my question (...Along the same line, if I want to use ...")? Otherwise, perhaps it should be re-posted in the Forms forum.

Thank you.