Possible to display more than one column's data in a lookup field? 1

[ottonomy]

I am editing an Access 2007 database created by someone else. One of the forms has a lookup field which pulls data from four columns of a parent table. The bound column is a numeric ID value, and the others are "last name", "first name", and "business name". Not all of the records in the parent table have values in all three data columns. When viewing the child table or form, only the records which have a "last name" value display anything in the fields for this lookup. When choosing a value for the field, all three columns are displayed in the pop-up list. Is there a way to make Access display all three columns in these fields, or at the very least, substitute a column with data when the "last name" column is empty for a particular record?

I am a novice with Access, so I hope my syntax is clear enough.

Thanks for any ideas,

Otto

 

[Remou]

Has this person committed look-ups:

http://www.mvps.org/access/lookupfields.htm

?

If so, you have problems.

It seems to me that the best way to set up what you want would be to set the Row Source to something like:

[tt]SELECT ID, LastName & ", " & FirstName & ", " & BusinessName
FROM TableName ORDER BY LastName, FirstName[/tt]

The above is the basic idea, it needs to be adjusted to suit you, and may need to be prettified to get rid of nulls, depending on your set-up.

http://lessthandot.com

 

[ottonomy]

This is what my row source looks like (This is in the design-view GUI of Access 2007, the Lookup tab of Field Properties. Is there another way of viewing it that will make the syntax look more like yours?):

SELECT [People].[Person], [People].[LastName], [People].[FirstName], [People].[BusinessName] FROM [People] ORDER BY [LastName], [FirstName], [BusinessName];

People is the name of the parent table. Person is the bound ID column.

I am not well versed in Access, and am just giving myself a crash course on the aspects of it that impact this project. As such, I don't really know what process was used to create these lookups, or how to determine that. Why is my row source peppered with brackets? Is this because I'm not working with the raw code? Is that what everyone here does?

 

[Remou]

First make a copy of the form, a little paranoia never hurts

Then paste this instead of your line:

SELECT p.[Person], p.[LastName] & ", " & p.[FirstName] & ", " & p.[BusinessName] FROM [People] AS p ORDER BY p.[LastName], p.[FirstName], p.[BusinessName];

Have a look around your form and make sure everything is working as it should.


------------------------------------
I have used an alias for People:

[tt]People AS p[/tt]

Which means I can say:

[tt]p.LastName[/tt]

Which is a little easier to read.

http://lessthandot.com

 

[ottonomy]

Thank you, Remou! That works perfectly! You have just made a couple hundred people very happy.

Otto