sorting multiple fields into one order?

[Laurie]

I'm sure this problem has come up many times, so there must be a simple
answer I am overlooking. I have a database of vendors that has fields
for company name and contact person name (last and first). However, for
some vendors there is no company name used, only a contact person. Is
there any way to sort the list alphabetically pulling from both the
company name field and the contact person last name field? I have
searched everywhere for an answer and haven't found a way to do this.
The best I can come up with is creating a new field for sort order. If
that's the route I have to go, I need some advice about how to automate
the process of assigning a value to the sort order.
Thanks for your help.

 

[Nikos Yannacopoulos]

Laurie,

I suppose you want this for the rowsource in a listbox or combo on a form?
Well, instead of using the table directly, you can use a query on it, ans
add a calculated field like:

IIf(IsNull([CompanyName], [LastName] & ", " & [FirstName], [CompanyName])

Then you can sort on this field, as well as display this column in your
listbox/combo while having the record ID field as the bound column.

HTH,
Nikos