Multicolumn lookup in one table from another.

[Guest]

I'm creating a table one of whose fields has a lookup that includes 2 columns
from another table. I need these two columns to display together in forms
based on the created table. The table allows me to include two or more
columns with the lookup wizard but I'm unable to get both columns to show in
a query or form based on the table. If I create a combo box in a form, the
combo box only brings in the first column. Is there a way to include both
columns?

 

[Wayne Morgan]

If you don't need to edit the displayed column, you could create a
calculated field that concatenates both columns together into one, leaving
you with a single column combo box. If you do need to be able to edit the
combo box (other than just making a selection from the values in the list),
you will need to place a textbox next to the combo box. Set the Control
Source of the textbox to the desired column in the combo box.

Example:
=cboMyCombo.Column(1)

The Column property is zero based, so 1 is the 2nd column.

 

[Guest]

So, Wayne. I do not want to edit the combo box, either column. So how do I
do this calculated concatenation thing?

 

[Guest]

I've tried concatenating the 2 fields and that worked except that one field
is numeric and one is text and thus Access gives an error message each time a
concatenated value is selected. Any other suggestions?

 

[Wayne Morgan]

Are you concatenating using "+" or "&". While the "+" will work with
strictly text values, it will cause a problem with numeric ones because
Access will try to Add instead of Concatenate. If that is the case, use "&"
instead to do the concatenation.

 

[Guest]

Actually did use the & in the expression. Couldn't makeit work. I did
however go back out to the Excel document and concatenated the cells there
and reimported the table. That worked. Thanks. Any other ideas on this
would be appreciated. H

 

[Wayne Morgan]

What do you have for the Row Source of the combo box? If it is a SQL
statement, please post it. If it is a stored query, please post the SQL view
of that query.

 

[Guest]

Well Wayne, Soon as I found something that worked I trashed the other
attempts. My problem is I haven't had time to read Viescas's book on Access.
I think I'll be able to go with the concatenation in Excel solution for now.
Thanks for sticking with this so long. H.